Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Formula | Selects Other Measure based on Value

 

Hi, I'm quite new to MS Power BI - Tools, so sorry in case I didn't pick exactly the right subject/location.

 

Following issue I can't get to work:

- I created two measures in Power Pivot (ACT_NET_% and ACT_GROSS_%) which calculate a percentage, they work fine

- I want to create a new measure, which selects based on a value (GROSS_NET) one of those measures

- basically a new column should display the ACT_GROSS_% in case the GROSS_NET value is 1 and the ACT_NET_% in case it is 2 (or not 1)

 

I tried the following, which returns an error messages:

 

ACT_%_NEW:=IF('HIERARCHY'[GROSS_NET]=1;[ACT_GROSS_%];[ACT_NET_%])

ACT_%_NEW2:=SWITCH('HIERARCHY'[GROSS_NET];1;[ACT_GROSS_%];2;[ACT_%])

 

Any help to shed some light into it would be very much appreciated. Thanks in advance!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    sorry, there is a mistake when post the formula, try this

    ACT_%_NEW:=IF(MAX('HIERARCHY'[GROSS_NET])=1 , [ACT_GROSS_%] , [ACT_NET_%])

    Best Regards,
    Lin

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    HI, Anonymous 

    You can use this formula to add a measure

    ACT_%_NEW:=IF(SELECTEDVALUE('HIERARCHY'[GROSS_NET]=1) , [ACT_GROSS_%] , [ACT_NET_%])

    Best Regards,

    Lin

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Lin,

      thanks for the reply. Works fine in power bi. Is there also an "old way" of doing it directly in power pivot, where the selectedvalue function is not available?

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous 

        Just use MAX instead of selectedvalue 

        ACT_%_NEW:=IF(MAX('HIERARCHY'[GROSS_NET]=1) , [ACT_GROSS_%] , [ACT_NET_%])

        Best Regards,

        Lin