MAX, IF, SELECTEDVALUE and filtering using related table

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.