Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

A table of multiple values was supplied where a single value was expected. (when ALL() is applied)

In short:

I want to calculate the cumulative sum of a column based on a filter on a reference number. 

I receive the error: MdxScript(Model) (74, 43) Calculation error in measure 'Measures 1'[Test 2 AG Total MGO (LS)]: A table of multiple values was supplied where a single value was expected. 

 

My calculation is: 

AG Total MGO (LS) = Calculate(
Sum( factAccountingTransactions[amount]);
factAccountingTransactions[account] >= 15100;
factAccountingTransactions[account] <= 15190;
FILTER( 'factAccountingTransactions'; ALL((factAccountingTransactions[dim_5])) <= MAX(factAccountingTransactions[dim_5])))
 
Intention with this formula is to SUM the amounts in [amount], for the given [account]s (between 15100-15190) for each [dim_5]. 
As the sum for MAX([dim_5] should be cumulative, I have added the FILTER as above. 
 
Example of what the table looks like: 
accountamountdim_5
15134-4718,65GRL053
15134-5234,27ICE006
151345234,27DPN006
1513441580BCE182
15134-128155,64IRL070
15134-127615,1IRL069
1513379597IRL069
1513327633,35ICE006
 
 
I would like this example to return this sum table (): 
 
dim_5amount
GRL053-4718,65
ICE00622399,08
DPN0065234,27
BCE18241580
IRL069-48018,1
IRL070-176173,74
 
IRL069 and IRL070 is an example of the running sequence and also where IRL070 should return the cumulative value of SUM of IRL069+IRL070. 
 
A few observations from my side which could be an issue? 
- [dim_5] is text, but I see PowerBI is able to sort it by the number value, and I assume it could also find MAX? 
          I have also tried sorting by a numerical ID instead of the [dim_5], but with the same error. 
- There may be multiple rows for each [dim_5] - ref above ICE006 and IRL069
- The visualization per [dim_5] works if not including ALL(), but then it obviously does not perform what I want (ie it is not cumulative).
1 ACCEPTED SOLUTION

The main issue is that you have your ALL in the wrong spot. That's what's generating your error. But, I would do it this way:

 

Total Cumulative Amount = 
VAR __max = MAX([ID])
RETURN
Calculate(
Sum( 'Table22'[amount]), 
FILTER(ALL('Table22'), 'Table22'[ID] <= __max))

See Page 13, Table 22


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I have now simplified the example table a bit more to try to find the error. 

 

This is now the current table, and I still cannot present a table visual. 

 

accountamountdim_5Voy_IDdim_2ID
15134-4718,65GRL05324233GRL1
1513327633,35ICE00679265ICE2
151345234,27DPN00689988DPN3
1513441580BCE18235839BCE4
1513379597IRL06931933IRL5
15134-128155,64IRL07053304IRL6

 

 

My formula is now simplified to 

Total Cumulative Amount  = Calculate(
Sum( 'Table'[amount]);
FILTER( 'Table'; ALL('Table'[ID]) <= MAX('Table'[ID])))
 
I now base the filtering on the ID instead to see if it is the Letter+number combinations which is making problems. But so far, no luck. Still get the same error message. 

The main issue is that you have your ALL in the wrong spot. That's what's generating your error. But, I would do it this way:

 

Total Cumulative Amount = 
VAR __max = MAX([ID])
RETURN
Calculate(
Sum( 'Table22'[amount]), 
FILTER(ALL('Table22'), 'Table22'[ID] <= __max))

See Page 13, Table 22


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you @Greg_Deckler

 

but it doesn't work. 

 

The total sum of the column [amount] in the example is 21.170,33. 

 

I'm now using the formula as suggested; 

 

Total Cumulative Amount =
VAR _max = MAX('Table'[ID])
RETURN
Calculate(
Sum( 'Table'[amount]);
FILTER( ALL('Table'); 'Table'[ID] <= _max))
 
This returns this table: 
 
dim5Total Cumulative Amount
BCE18221170,33
DPN00621170,33
GRL05321170,33
ICE00621170,33
IRL06921170,33
IRL07021170,33
Total21170,33

 

 

Anonymous
Not applicable

Had to specify column for ALL and not apply it for the full table. 

 

So this formula solved it: 

 

Total Cumulative Amount =
VAR _max = MAX('Table'[ID])
RETURN
Calculate(
Sum( 'Table'[amount]);
FILTER( ALL('Table'[ID]); 'Table'[ID] <= _max))

 

 

Thank you all for your input! 

v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 


 IRL069 and IRL070 is an example of the running sequence and also where IRL070 should return the cumulative value of SUM of IRL069+IRL070. 
 

How to determine "running sequence"? In above example, as IRL069 and IRL070 share the same beginning alphabet "IRL" and 69, 70 are continual numbers, we should calculate cumulative total, right? 

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi  @v-yulgu-msft

 

The IRL is identifying a vessel, while the number identifies a voyage, and it will follow the sequence IRL069 - IRL070 - IRL071 etc. 

 

And yes - we are looking for cumulative total. 

Greg_Deckler
Super User
Super User

I believe you want:

 

AG Total MGO (LS) = Calculate(
Sum( factAccountingTransactions[amount]); 
factAccountingTransactions[account] >= 15100; 
factAccountingTransactions[account] <= 15190;
FILTER( ALL('factAccountingTransactions'); factAccountingTransactions[dim_5] <= MAX(factAccountingTransactions[dim_5])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks @Greg_Deckler

 

but unfortunately that does not solve it. It actually gives quite unexpected results, which I cannot really make sense of. 

I manage to display a visual, but it will not be filtered by dim_5. It will be a sum of all rows on each dim_5 (same value all over). 

 

(Edit post after a bit of testing)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.