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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nmcclary
Helper II
Helper II

Compare Measure to Column and Return Value

Hello,

 

I have a measure that calculates a % of locked charts.

 

I have the below table to compare the % of locked charts (health care charts) and I want to return the compensation %:

 

Chart Lock %  |  Compensation %

0                              0

.7                             .25

.8                             .5

.85                           .75

.9                             1.0

 

I want to compare the achievment of the measure against the item completion column using measure > item completion and then returning to correlating compensation % as a value.

 

I know I could do this simply by writing a formula and hardcoding the numbers, however I need to do this for 4-5 measures that all have different thresholds for compensation. These thresholds can also change based on the provider specialty, requiring multiple measures. Lastly, other measures will include imported peer data that will be updated on a monthly basis so I don't want to have to go back and rewrite the measure every month.

 

How can I write this in DAX?

 

Thanks.

 

3 REPLIES 3
V-pazhen-msft
Community Support
Community Support

@nmcclary 
To compare measure with column, try this:

 

Measure2 =
IF ( [measure] > MAX ([item completion]), MAX([compensation %])

 

However, it sounds like you have a complex model with many measures. Usually, sample pbix is appreciated to help us understand your requirements.

 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@V-pazhen-msft  That seems to be somewhat on the right track. For those who score above .9 on ChartLock, it shows the payout at 100% but I assume since it's listed at Max, it will only show to Max.

 

I don't really have anything else loaded into the model besides the measure and the comp chart above. I'm waiting to see if I can figure this out before I recommend moving from the more manual excel process to Power BI.

Bump

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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