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 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
                    )
                )
            

)
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
            )
        )
    )

     

6 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        d_gosbell
        Super User
        7 years ago

        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
            )
        )
    )