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

     

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

       

      • 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!