Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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!

DAX Formula.PNG

1 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

hi, @Anonymous 

Just use MAX instead of selectedvalue 

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

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.