Forum Discussion

Longdisplay's avatar
Longdisplay
Regular Visitor
5 years ago
Solved

PowerBI calculated measure - DAX

Hi Everyone, I have created a measure which calculates KPI based on selection in Slicer; when the particular product "A" or "B" or "C" is selected then the measure value returns, but when I have two different values selected in slicer like (A and B) - my calculated measure returns an error. My Slicer has distinct values like A,B,C to make selection. Please suggest - Below is my calculated measure :

TARGET =
IF (
    ISCROSSFILTERED ( Product[Product_Type] ),
    SWITCH (
        TRUE (),
        VALUES ( Product[Product_Type] ) = "A",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] <> "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            )
                + (
                    (
                        CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] = "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                    ) * ( 0.43 )
                ),
        VALUES ( Product[Product_Type] ) = "B",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.07
            ),
        VALUES ( Product[Product_Type] ) = "C",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            ),
        BLANK ()
    ),
    BLANK ()
)

Greg_Deckler,@v-jiascu-msft Anonymous selimovd Fowmy amitchandak parry2k Jihwan_Kim 

DAX
DynamicMeasure
ISCROSSFILTERED
MULTIPLESELECTION
Slicer
switch
  • parry2k's avatar
    parry2k
    5 years ago

    Longdisplay I will revisit this measure and maybe rewrite it this way. Again, please double-check the results.

     

     

    TARGET =
IF (
    ISCROSSFILTERED ( Product[Product_Type] ),
    SUMX ( VALUES ( Product[Product_Type] ) ),
    
    SWITCH (
        Product[Product_Type],
        "A",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] <> "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            )
                + (
                    (
                        CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] = "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                    ) * ( 0.43 )
                ),
       "B",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.07
            ),
       "C",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            ),
        BLANK ()
    ) --switch closing
    ), --sumx closing
    BLANK ()
)

     

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

5 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Longdisplay ,

     

    you get the error because you are checking with multiple selections a list returned by VALUES to a string, for example "A".

    As long as there is only one value that works as it's transformed to a single row, but when there are more values you will get an error.

     

    First I would change the VALUES ( Product[Product_Type] ) to MAX( Product[Product_Type] ). This should fix the error.

     

    If you really want to select only one value, I would already change the slicer to single selection:

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

    • Longdisplay's avatar
      Longdisplay
      Regular Visitor
      5 years ago

      Hi selimovd ; I am getting inaccurate total from calculated measure when I have Product A and Product B Selected together - for example 

      Total (from measure)       143,661
      Product A       129,642
      Product B          17,555
      Accurate Total should be       147,197

       

      Can you please suggest? 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Longdisplay I will revisit this measure and maybe rewrite it this way. Again, please double-check the results.

     

     

    TARGET =
IF (
    ISCROSSFILTERED ( Product[Product_Type] ),
    SUMX ( VALUES ( Product[Product_Type] ) ),
    
    SWITCH (
        Product[Product_Type],
        "A",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] <> "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            )
                + (
                    (
                        CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] = "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                    ) * ( 0.43 )
                ),
       "B",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.07
            ),
       "C",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            ),
        BLANK ()
    ) --switch closing
    ), --sumx closing
    BLANK ()
)

     

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    In my opinion, instead of using VALUES, try using SELECTEDVALUES.

    In that case, I think, if more than two products are selected in the slicer, it will return a blank.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Jihwan_Kim But that is not a true fix, it will not work if user selects more than one value, better to have multiple values selected, and show sum as Longdisplay illustrated in his screenshot. Just my 2 cents.