DAX Formula | Selects Other Measure based on Value

[Anonymous]

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!

[v-lili6-msft]

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]

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]

hi, @Anonymous 

Just use MAX instead of selectedvalue 

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

Best Regards,

Lin

[Anonymous]

Hi Lin, getting a semantic error, that MAX only accepts a column reference as an argument

[v-lili6-msft · Accepted solution]

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