Forum Discussion

simuruge's avatar
simuruge
Regular Visitor
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.

Need Help
Show and Tell
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    simuruge 

     

    Try something like this

     

    Measure =
VAR _toggleValue =
    CALCULATE (
        SELECTEDVALUE ( 'Table1'[Toggle values] )
    )
VAR _version =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[version] )
    )
VAR _add =
    IF (
        _togglevalue = _version,
        SUM ( 'Table'[Perf $] ),
        0
    )
VAR _sum =
    SUM ( 'Table'[Plan $] ) + _add
RETURN
    _sum

     

    Can you provide sample data and expected output.

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

5 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi simuruge 

     

    you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

     

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    simuruge 

     

    Try something like this

     

    Measure =
VAR _toggleValue =
    CALCULATE (
        SELECTEDVALUE ( 'Table1'[Toggle values] )
    )
VAR _version =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[version] )
    )
VAR _add =
    IF (
        _togglevalue = _version,
        SUM ( 'Table'[Perf $] ),
        0
    )
VAR _sum =
    SUM ( 'Table'[Plan $] ) + _add
RETURN
    _sum

     

    Can you provide sample data and expected output.

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • simuruge's avatar
      simuruge
      Regular Visitor
      6 years ago

      Hi Nandu,

       

      Thanks for your answer.

       

      I tried the below logic but it was not working. I have simplifed the output to give me either 1 or 0 based on condition.

       

      test =
      var _toggleValue = selectedvalue( table[Toggle values])
      var _version = CALCULATE(SELECTEDVALUE([Version]))
      var output = IF(_toggleValue = _version,1,0)
      return output
       
      I am pulling in a table visual with  columns, version and test. 
       
      It always returns 0 for all the rows even if they are equal to toggle values.
       

      Regards,

      Sid

      • nandukrishnavs's avatar
        nandukrishnavs
        Community Champion
        6 years ago

        simuruge 

         

        Test this 

        test =
VAR _toggleValue =
    CALCULATE (
        SELECTEDVALUE ( table[Toggle values] )
    )
VAR _version =
    CALCULATE (
        SELECTEDVALUE ( [Version] )
    )
VAR output =
    IF (
        _toggleValue = _version,
        1,
        0
    )
RETURN
    output



        Did I answer your question? Mark my post as a solution!
        Appreciate with a kudos         🙂