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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Dax query returns unexpected result

Hi everyone,

 

I have a pbix file ContosoSales-Excerpt.pbix being only 'Product' table of Contoso Sales sample. Then I made a Dax query by Dax Studio which calculates cumulative total of ‘Product’ [UnitPrice] by the ascending order of ‘Product’ [ProductSubCategoryKey].

The query is simple enough as below.

 

2020-12-17_08h31_41.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This works very fine as expected. It returns below as output

 

2020-12-17_08h32_29.png

 

 

 

 

 

 

 

 

 

 

But if I commented out line #7 of the query,

 

2020-12-17_08h39_40.png

 

 

 

 

 

 

 

 

 

All row’s AccUnitPrice return same values.

 

2020-12-17_08h40_02.png

 

 

 

 

 

 

 

 

 

 

 

 

 

It seems that the return values are the sum of unit price of all but the one whose ProductSubCategoryKey  is maximum (in this case #35 ) in the filter [BrandName]=”Contoso” && [ClassName]=”Regular”.

 

I cannot understand what is going on in this query. I would like to know in what kind of context AccUnitPrice of each row is evaluated and how it is calculated like this.

 

Any suggestions would be appreciated.

Thank you in advance.

1 ACCEPTED SOLUTION

Hi @Usagi_Nakamura ,

 

Answering to your last question:

 

Not that I am sure this is Auto-Exist anymore but in the example above, where you exchanged Product[UnitPrice] by Sales[UnitPrice] you were still having all the ingredients to trigger it according to this:

 

https://docs.microsoft.com/en-us/dax/all-function-dax


"An example where auto-exist and ALL() provide unexpected results is when filtering on two or more columns of the same table (like when using slicers), and there is a measure on that same table that uses ALL()."

 

In your last example ProductSubcategoryKey (ALL), ClassName and BrandName (SLICERS) are still from the same table. Now, while moving ClassName and BrandName to a different table could sort out the issue, we still want to learn why this is happening.

 

I leave it to the experts, but hoping this is of any help, I reproduced the scenario in PBI with the simplest table possible (just two rows, only one brand/class and no duplicates) to rule out possible causes:

 

Payeras_BI_0-1608572447688.png

 

The measure that breaks:

 

 

SUMX = 
VAR CurrentKey = SELECTEDVALUE('Table'[Key])
RETURN
SUMX(
    FILTER(
        ALL('Table'[Key]);
        'Table'[Key]<CurrentKey
    );
    [Sum of UnitPrice]
)

 

 

A measure that works: 

 

 

With Calculate = 
VAR CurrentKey = SELECTEDVALUE('Table'[Key])
RETURN
CALCULATE(
    [Sum of UnitPrice];
    'Table'[Key]<CurrentKey
)

 

 

In PBI:

 

- A Table visualization with only one of the columns used to slice shown:

 

Payeras_BI_0-1608569266732.png

 

- Ok to slice by the one not present in the visualization:

 

Payeras_BI_4-1608569747679.png

 

- But as soon you slice by both of them the measure with SUMX breaks:

 

Payeras_BI_3-1608569691570.png

 

Regards,

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

14 REPLIES 14

Hi @AllisonKennedy 

 

One thing I did for to think about this topic.

I tried several patterns of filter conditions and run the query.

 

Let me share the result as below.

With line #7 (= ,'Product'[BrandName] ) , all the result are natural and easy to understand.

But without line #7, I feel there are some regularities in calculations but I cannot express it in a sentence.

 

2020-12-21_14h22_29.png 

 

 

 

 

 

 

 

AA) changing to "'Product'[ProductSubcategoryKey] = CurrentSubKey"

 

<<Without line #7>>

2020-12-21_12h49_10.png

 

 

 

 

 

<<With line #7>>

2020-12-21_12h49_29.png

 

 

 

 

 

 

BB) changing to "'Product'[ProductSubcategoryKey] <> CurrentSubKey"

 

<<Without line #7>>

2020-12-21_12h50_08.png

 

 

 

 

 

<<With line #7>>

2020-12-21_12h50_28.png

 

 

 

 

 

CC) changing to "'Product'[ProductSubcategoryKey] = CurrentSubKey + 10"

 

<<Without line #7>>

2020-12-21_12h54_36.png

 

 

 

 

 

 

<<With line #7>>

2020-12-21_12h52_34.png

 

 

 

 

 

 

DD) changing to "'Product'[ProductSubcategoryKey] = 11"

 

<<Without line #7>>

2020-12-21_12h56_48.png

 

 

 

 

 

 

<<With line #7>>

2020-12-21_12h57_06.png

 

 

 

 

 

 

I am sorry but I am not sure that it is meaningful...

 

Regards,

@Usagi_Nakamura  Yes, that is meaningul. That's basically similar to what I was trying to express in sentence in one of my earlier posts - the filter context of the subcategorykey seems to require that brandname row context, but only in SUMX and not in CONCATENATEX. That is why I added the CurrentSubcategoryKey column to my test query, but it didn't shed much light on the situation. I've put the call out for more input, will see if we get any other brains responding here, but thanks for keeping this thread going. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy  again,

 

Thank you very much for your comment.

For now, I would like to share to viewers the result from the revised senario into which you kindly add items in your previous post.

This shows certainly well the contrast between Countrows and Countx.

 

<<With line #7>>

2020-12-19_21h44_13.png

 

 

 

 

 

 

 

 

 

 

 

 

 

<<Without line #7>>

2020-12-19_21h39_27.png

 

 

 

 

 

 

 

 

 

 

 

 

Kind regards,

 

Hi @Payeras_BI 

Muchas gracias for your help. That’s superbe!

 

But my query was at first summing ‘Sales’[SalesQuantity] and filtering ‘Product’[ClassName], ‘Product’[BrandName] and ‘Product’[ProductSubCategoryKey].

I doubted that the problem comes from relation matter ( for example, relation “both” etc. ) .So in order to eliminate impact of the relation, I made the model to single table one.

 

Anyway I will check my previous code.

 

Thank you again for your continuous help!

 

Regards,

 

Hi @Payeras_BI ,

 

I am sorry. I totally misunderstood what you said.

 

Even if  we sum a column of another table instead of 'Product'[UnitPrice], we might not be able to get rid of this problem.

The problem might be coming from the fact that more than two filters are in one table.

 

The query for two table ( 1 Dimension ( Product ) + 1 Fact ( Sales ) but the same thing happens like this.

 

2020-12-18_22h16_01.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards,

Hi @Usagi_Nakamura ,

 

Answering to your last question:

 

Not that I am sure this is Auto-Exist anymore but in the example above, where you exchanged Product[UnitPrice] by Sales[UnitPrice] you were still having all the ingredients to trigger it according to this:

 

https://docs.microsoft.com/en-us/dax/all-function-dax


"An example where auto-exist and ALL() provide unexpected results is when filtering on two or more columns of the same table (like when using slicers), and there is a measure on that same table that uses ALL()."

 

In your last example ProductSubcategoryKey (ALL), ClassName and BrandName (SLICERS) are still from the same table. Now, while moving ClassName and BrandName to a different table could sort out the issue, we still want to learn why this is happening.

 

I leave it to the experts, but hoping this is of any help, I reproduced the scenario in PBI with the simplest table possible (just two rows, only one brand/class and no duplicates) to rule out possible causes:

 

Payeras_BI_0-1608572447688.png

 

The measure that breaks:

 

 

SUMX = 
VAR CurrentKey = SELECTEDVALUE('Table'[Key])
RETURN
SUMX(
    FILTER(
        ALL('Table'[Key]);
        'Table'[Key]<CurrentKey
    );
    [Sum of UnitPrice]
)

 

 

A measure that works: 

 

 

With Calculate = 
VAR CurrentKey = SELECTEDVALUE('Table'[Key])
RETURN
CALCULATE(
    [Sum of UnitPrice];
    'Table'[Key]<CurrentKey
)

 

 

In PBI:

 

- A Table visualization with only one of the columns used to slice shown:

 

Payeras_BI_0-1608569266732.png

 

- Ok to slice by the one not present in the visualization:

 

Payeras_BI_4-1608569747679.png

 

- But as soon you slice by both of them the measure with SUMX breaks:

 

Payeras_BI_3-1608569691570.png

 

Regards,

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Hi @Payeras_BI 

 

Thank you very much for your latest post which is really helpful to me and I am sorry for replying late.

I tried to map the cases in your post to corresponding my dax query and summarize the result.

 

<<In your case 1>>

image.png

 

 

 

 

 

 

 

 

In this case, One Grouping column (Class) + No filer => SUMX is OK

 

Corresponding dax query is
2020-12-25_06h27_52.png

 

 

 

 

 

 

 

 

<<In your case 2>>

image.png

 

 

 

 

 

 

 

 

 

 

 

One grouping column ( Class ) + 1 Filter Column ( Brand ) => OK

Corresponding dax query is

image.png

 

 

 

 

 

 

 

 

<<In your case 3>>

image.png

 

 

 

 

 

 

 

 

 

One grouping column ( Class ) + 2 Filter Column ( Class, Brand ) => NG

Corresponding dax query is

image.png

 

 

 

 

 

 

 

 

 

 

 

I tried some other cases and summarizing the result as below.

 

image.png

 

Thank you Kind regards, 

 

Hi @Payeras_BI ,

 

Thank you for giving me a hint which must be helpful.

I will try to understand the concept of dax-auto-exist and to apply it to this matter.

 

Kindest regards,

 

Hi again @Usagi_Nakamura ,

 

To be honest I also struggle to understand why the 51.663,53 for every row, let me know if you find out.

 

But, at least, if you follow the advice at the end of the article you get rid of the issue:

 

"In simpler data models with only one table and with a fancy data distribution of values, it might be possible to run into auto-exist problems. When this happens, the easiest solution is to avoid using a single table and to build a proper star schema instead."

 

Payeras_BI_0-1608245384457.png

Exact same query but now slicing on columns belonging to the dimension tables:

Payeras_BI_1-1608245413960.png

 

Regards,

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Hi @AllisonKennedy ,

Thank you very much for your kind suggestions!

 

Actually I knew that with your query, it work well.

In addition, your query, even if without line #63, can get same result.

 

2020-12-17_21h23_08.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In fact, my query was originally matrix visual and SumUnitPrice and AccUnitPrice were calculated measures and BrandName filter was coming from a Slicer visual. The query is made from such visuals,  using power bi perfomance analyzer.

 

 

Moreover, for my query, I tried to change SUMX to CONCATENATEX and found that with or without line #7, same value is returned in every row. These values should be items to be calculated in SUMX version. But if I change again to Sumx, return values are different between with line #7 and without line #7.

 

<< With line #7>>

 

2020-12-17_21h24_44.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<<Without line #7>>

 

2020-12-17_21h32_49.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I tried to find out why it is possible but in vain for three months...

 

Thanks.

@Usagi_Nakamura  I see your problem now, interesting that CONCATENATEX behaves differently than SUMX. To be honest, this query is multi-layered and not best practice optimised, so I would change it, but I am fascinated to learn from it. It's definitely helping me understand better some of the behaviour, and I have run into scenarios where SUMX doesn't behave as I expected and this might give insight to why. 

 

Just for fun, here are some additional examples to add to your scenario. It seems COUNTROWS behaves as CONCATENATEX, while COUNTX behaves as SUMX. Try also commenting out the TREATAS that uses brand name and note that this also fixes things. The problem arises when you have a filter/relationship on a column that does not also exist in the table, and then try to use aggregate iterators: 

 

1 = SUMMARIZECOLUMNS('Product'[ProductSubcategoryKey], 'Product'[ClassName]
--, 'Product'[BrandName]
,TREATAS({"Contoso"}, 'Product'[BrandName]),treatas({"Regular"}, 'Product'[ClassName]), "SumUnitPrice", CALCULATE(SUM('Product'[UnitPrice]))
, "AccUnitPrice",
VAR CurrentSubKey = VALUES('Product'[ProductSubcategoryKey])
RETURN
SUMX(FILTER(
ALL('Product'[ProductSubcategoryKey]),
'Product'[ProductSubcategoryKey]<CurrentSubKey
),
CALCULATE(SUM('Product'[UnitPrice])))
, "ConcateUnitPrice",
VAR CurrentSubKey = VALUES('Product'[ProductSubcategoryKey])
RETURN
CONCATENATEX(FILTER(
ALL('Product'[ProductSubcategoryKey]),
'Product'[ProductSubcategoryKey]<CurrentSubKey
),
CALCULATE(SUM('Product'[UnitPrice])), ";")
, "SubCategory", VALUES('Product'[ProductSubcategoryKey])
, "Count Unit Price",
VAR CurrentSubKey = VALUES('Product'[ProductSubcategoryKey])
RETURN
COUNTX(FILTER(
ALL('Product'[ProductSubcategoryKey]),
'Product'[ProductSubcategoryKey]<CurrentSubKey
),
CALCULATE(SUM('Product'[UnitPrice])))
, "CountRows", VAR CurrentSubKey = VALUES('Product'[ProductSubcategoryKey])
RETURN
COUNTROWS(FILTER(
ALL('Product'[ProductSubcategoryKey]),
'Product'[ProductSubcategoryKey]<CurrentSubKey
))
, "SUMX",
VAR CurrentSubKey = SELECTEDVALUE('Product'[ProductSubcategoryKey])
RETURN
SUMX(FILTER(
ALL('Product'[ProductSubcategoryKey]),
'Product'[ProductSubcategoryKey]<CurrentSubKey
),
CALCULATE(SUM('Product'[UnitPrice])))
)
 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @Usagi_Nakamura ,

 

From your last explanation this behaviour seems to respond to what @AlbertoFerrari explains here https://www.sqlbi.com/articles/understanding-dax-auto-exist/

 

Regards,

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
AllisonKennedy
Super User
Super User

@Usagi_Nakamura  You can try this instead: 

 

Table = SUMMARIZECOLUMNS('Product'[ProductSubcategoryKey], 'Product'[ClassName]
--, 'Product'[BrandName]
,TREATAS({"Contoso"}, 'Product'[BrandName]),treatas({"Regular"}, 'Product'[ClassName]), "SumUnitPrice", CALCULATE(SUM('Product'[UnitPrice])), "AccUnitPrice",
VAR CurrentSubKey = VALUES('Product'[ProductSubcategoryKey])
RETURN
// SUMX(FILTER(
// ALL('Product'[ProductSubcategoryKey]),
// 'Product'[ProductSubcategoryKey]<CurrentSubKey
// ),
// CALCULATE(SUM('Product'[UnitPrice]))))

CALCULATE(
SUM
('Product'[UnitPrice]), ALL('Product'[ProductSubcategoryKey]), 'Product'[ProductSubcategoryKey]<CurrentSubKey))
 
You can see I have commented out what you had and replaced it with just SUM rather than SUMX.
 
This is still not a terribly efficient expression, so further room to improve it, but I'm not sure what your end goal is?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@Usagi_Nakamura  This is a very complex function -there's a lot going on. First, it's using CALCULATE to enable you to use SUM within a calculated column. The SUMMARIZECOLUMNS function creates a table with 2 calculated columns: "SumUnitPrice" and "AccUnitPrice". It creates these 2 columns within the context of the table it builds - when you comment out line 7, you remove the context of the ProductBrand, which impacts the context within which the SUMX is calculated. Since you have a CALCULATE(SUM() within that SUMX to calculate AccUnitPrice, this context change of removing ProductBrand matters. 

Did I make any sense? 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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