MAX, IF, SELECTEDVALUE and filtering using related table

[ArvindSh-MSFT]

I stumbled upon a tricky issue which I can reproduce using the AdventureWorks Sales.pbix sample. Here are the repro steps:

1. Download the sample from https://github.com/microsoft/powerbi-desktop-samples/

2. Using "Enter data" added a simple table called "ApplyDiscount" with 2 values: Yes, No:

At this point, here's the relevant portion of the diagram:

3. Create 2 measures under the Sales table. Ignore if there is a functional difference between these 2, I'm just using them to illustrate the general pattern.

FaultyMeasure = MAX(IF(SELECTEDVALUE(ApplyDiscount[ApplyDiscount]) == "Yes", sum(Sales[Total Product Cost]), 100), SUM(Sales[Sales Amount]))

OKMeasure = IF(SELECTEDVALUE(ApplyDiscount[ApplyDiscount]) == "Yes", MAX(SUM(Sales[Sales Amount]),100), sum(Sales[Total Product Cost]))

4. Then create a new report page, add a single-select slicer for ApplyDiscount and a regular multi-select slicer for Table[Category]:

5. Add a matrix as follows:

6. At this time, it works as expected. Selecting specific Table[Product] results in only the corresponding row being displayed in the matrix.

7. Now, add FaultyMeasure to the matrix, we now see entries for the other Category which is unexpected:

This seems strange to me. I'm guessing it is due to the specific combination of IF, MAX and SELECTEDVALUE of the unrelated table. I can eventually achieve what I need using the OKMeasure. But curious to understand what exactly is causing the FaultyMeasure to behave like this.

Thanks in advance.

[Wilson_ · Accepted solution]

Hi ArvindSh,

If you format your DAX so it's more readable, it's clearer what the issue is. As mentioned, there is a functional difference between the two measures. I'm not sure why you're asking to ignore that. That is the entire reason the two give different results.

OKMeasure = 
IF (
    SELECTEDVALUE ( ApplyDiscount[ApplyDiscount] ) == "Yes", 
    MAX ( 
        SUM ( Sales[Sales Amount] ),
        100
    ),
    SUM ( Sales[Total Product Cost] )
)

Here with OKMeasure, since you've selected "No" for ApplyDiscount, it just returns the sum of Total Product Cost.

FaultyMeasure = 
MAX (
    IF (
        SELECTEDVALUE ( ApplyDiscount[ApplyDiscount] ) == "Yes", 
        SUM ( Sales[Total Product Cost] ), 
        100
    ), 
    SUM ( Sales[Sales Amount] )
)

Here with FaultyMeasure, since you've selected "No" for ApplyDiscount and filtered for only Bikes, the logic on the other three categories is as follows:

FaultyMeasure = 
MAX (
    100, -- ApplyDiscount is "No", therefore IF function returns the second value, ie 100
    BLANK() -- you've filtered for Bikes so sum of Sales Amount is blank
)

The max of 100 and BLANK() is 100. Therefore for the other three product categories, the measure returns 100.

Please let me know if this was unclear!

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[ArvindSh-MSFT]

Thank you for sharing the tip about BLANK(). It certainly helped. But I'm still unclear on why the measure was even evaluated for the other unselected categories. My (perhaps naive) expectation was that the filters would always be applied?

[Wilson_]

Hi ArvindSh,

Great question. The simple answer is I don't know. 🙂

It seems like sometimes it happens and sometimes it doesn't happen and frankly, I'm not knowledgeable enough right now to know in advance whether or not it will happen. I just usually know what to look for when it happens.

If somebody else knows, I would learn something too. 😄

[ArvindSh-MSFT]

I received confirmation from a colleague which helped clarify that measures are evaluated for all possible filter context combination and the fact that it returned a non-blank result in specific cases, caused it to be shown.

This is a valuable learning and I'm glad it came up and helped fix the formula in my case.

[Ritaf1983]

Hi @ArvindSh-MSFT 

The fo formula Seems ok if the needed result is the largest number between 

 sum(Sales[Total Product Cost])/ 100 and SUM(Sales[Sales Amount]).

Can you please share the pbix itself with with your formulas and matrix?