Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How do I create a measure that returns two different values based on condition

Hi,

 

I am trying to trplicate a formula in Tableau like, ([Plan $] + IIF([Version]=[Version Toggle],[Perf $],0))

 

[Version] is a column in a table

[Version Toggle] is a dynamic value where the user can toggle between two values.

In PoweBI , I will use selected value to get the value selected by user.

 

Since Power BI measure returns only one value, I could not use formula like below,

 

sum([Plan $]) + If ( values([table.version]) = selectedvalue([Version Toggle]), sum([Perf $]) , 0)

 

Sample values: 

Version = 000, 001, 003, 004

Version Toggle values ,  000, 001

 

So, how do I achieve it?

 

Thanks in advance.

 

 

 

 

Help
power bi
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous 

     

    I create a simple sample. Please try the formulas to check if it is what you want.

     

    • For this formula, if we don't select any values in slicer, it will return 0 by default.
    Measure =
VAR Selected_Version =
    SELECTEDVALUE ( 'Table 1'[Version] )
VAR a =
    SUMX (
        'Table',
        'Table'[Plan]
            + IF ( 'Table'[Version] = Selected_Version, SUM ( 'Table'[Perf] ), 0 )
    )
RETURN
    a
    • If you want to show other data without selecting, you could try this:
    Measure 2 =
VAR Selected_Version =
    SELECTEDVALUE ( 'Table 1'[Version] )
VAR a =
    SUMX (
        'Table',
        'Table'[Plan]
            + IF ( 'Table'[Version] = Selected_Version, SUM ( 'Table'[Perf] ), 0 )
    )
RETURN
    IF ( ISFILTERED ( 'Table 1'[Version] ), a, "No Filter" )

     

    For more details, please download my sample to have a try.

     

4 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

     

    I create a simple sample. Please try the formulas to check if it is what you want.

     

    • For this formula, if we don't select any values in slicer, it will return 0 by default.
    Measure =
VAR Selected_Version =
    SELECTEDVALUE ( 'Table 1'[Version] )
VAR a =
    SUMX (
        'Table',
        'Table'[Plan]
            + IF ( 'Table'[Version] = Selected_Version, SUM ( 'Table'[Perf] ), 0 )
    )
RETURN
    a
    • If you want to show other data without selecting, you could try this:
    Measure 2 =
VAR Selected_Version =
    SELECTEDVALUE ( 'Table 1'[Version] )
VAR a =
    SUMX (
        'Table',
        'Table'[Plan]
            + IF ( 'Table'[Version] = Selected_Version, SUM ( 'Table'[Perf] ), 0 )
    )
RETURN
    IF ( ISFILTERED ( 'Table 1'[Version] ), a, "No Filter" )

     

    For more details, please download my sample to have a try.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, Thank you so much for the solution. Yes it works and what Nandu had mentioned also worked. 

       

      I made an error by pasting the formua in a calculated column. However, when I used a measure it worked fine.

       

      Thank you both for extending your help.

       

      Regards,

      Sid

      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        My formulas are both of measures. I'm glad that they works for you. 😊

         

        Can you please accept the helpful answer as  solution? Then we will be able to close the thread. More people who have the same request will benefit here.