Nested filtering question

[Anonymous]

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 perfectly except the requirement goes one step further and I need to display the total currency when a certain filter value is selected. I'm using SSAS as my source so I don't think I can use SELECTEDVALUE as it isn't supported in my version. See my measure below. It works perfectly except I need to add when the Locations[Continent] = "EMEA" is selected so a certain measure. How would I add this to my curreny measure. Any help is appreciated! 

Total Sales M:=
VAR local =
    CALCULATE ( SUM ('revenue'[revenue_local] ) )
VAR market =
    CALCULATE ( SUM ('revenue'[revenue_market] ) )
VAR usd =
    CALCULATE ( SUM ('revenue'[revenue_usd] ) )
RETURN
            IF (
                ISFILTERED ( Locations[Country] ),
                local,
                IF (
                    ISFILTERED ( Locations[Market] ),
                    market,
                    IF (
                        ISFILTERED ( Locations[Continent] ),
                    usd,
                    usd
                    )
                )
            

)

[d_gosbell]

You can do the equivalent of SELECTEDVALUE(Locations[Continent]) by doing something like the following

var currentContinent = IF( HASONEVALUE( Locations[Continent] ) , VALUES( Locations[Continent] ) )

[Anonymous]

Thanks for your reply. I tried that, and it got me a little further. But below is what I have where it actually does calculate the proper currency based on my filter. But I get the following message when I don't have anything select.

"A table of multiple values was supplied where a single value was expected" 

Any ideas how to resolve? 

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 (
                ISFILTERED ( location[cntnt_name] )
                    && VALUES( location[cntnt_name] )  = "EMEA",
                market,
                usd
            )
        )
    )

[d_gosbell]

You have not tried the code I suggested. You need to use the HASONEVALUE function to test if the filtered table one one or more value in order to avoid that error you are getting. (which is probably being thrown by the grand total row which will have mulitple cntnt_name values when you have cntnt_name on the rows)

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 (
                HASONEVALUE ( location[cntnt_name] )
                    && VALUES( location[cntnt_name] )  = "EMEA",
                market,
                usd
            )
        )
    )

[Anonymous]

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 · Accepted solution]

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]

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