Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Nested filtering question

Hello everyone,   I have a currency question. We have a data model that has 3 currencies. Local, Market and USD. I have a measure to display the currency based on the filter selection. It works per...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    Sorry, I thought they'd updated the IF function to do short circuit evaluation of boolean conditions, but it does not look like it does that (I might be confusing DAX and MDX here), so you'll need to nest the VALUES inside an IF checking for HASONEVALUE.

     

    eg.

     

    Continent Test = 
VAR local =
    CALCULATE ( SUM ( Sales[sales_amt_local] ) )
VAR market =
    CALCULATE ( SUM ( Sales[sales_amt_market] ) )
VAR usd =
    CALCULATE ( SUM ( Sales[sales_amt_usd] ) )
RETURN
    IF (
        ISFILTERED ( location[cntry_name] ),
        local,
        IF (
            ISFILTERED ( location[mrkt_name] ),
            market,
            IF (
                IF (HASONEVALUE ( location[cntnt_name] ), VALUES( location[cntnt_name] ), "")  = "EMEA",
                market,
                usd
            )
        )
    )

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Yes, that works as you can see from the pictures below. But, the desired result when NO filter is selected to use the calculation for the variable USD doesn't work. That is the part I'm struggling with. So, do I understand correctly that you're saying, the reason for that error is because when I don't have any filter selected, it's expecting a single value but is getting multiples because there are multiple values to filter? Are there no workarounds?

 

Thank you for you attention to this! 

 

d_gosbell's avatar
d_gosbell
Super User
7 years ago

Sorry, I thought they'd updated the IF function to do short circuit evaluation of boolean conditions, but it does not look like it does that (I might be confusing DAX and MDX here), so you'll need to nest the VALUES inside an IF checking for HASONEVALUE.

 

eg.

 

Continent Test = 
VAR local =
    CALCULATE ( SUM ( Sales[sales_amt_local] ) )
VAR market =
    CALCULATE ( SUM ( Sales[sales_amt_market] ) )
VAR usd =
    CALCULATE ( SUM ( Sales[sales_amt_usd] ) )
RETURN
    IF (
        ISFILTERED ( location[cntry_name] ),
        local,
        IF (
            ISFILTERED ( location[mrkt_name] ),
            market,
            IF (
                IF (HASONEVALUE ( location[cntnt_name] ), VALUES( location[cntnt_name] ), "")  = "EMEA",
                market,
                usd
            )
        )
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you for the help. This was a massive lifesaver!