cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Get Operator From Table & Use In A Measure

Hi all, I think I have a particularly tricky one to ask today. I am trying to obtain the number of sales from multiple stores and compare it to the targets that they have in place for each store and each month. To do this I am trying to retreive the less/greater than symbol from a table where the targets live and compare to the monthly sales from the stores. I want to try and do this in a measure.

The target table looks like the below. You can see the operator and target helps tell you how we rate the store's performance.

E.g. If the Alabama store sells more than \$45,000 then it is given green, if above \$40,000 (but less than \$45,000) then orange and less than \$40,000 is red.

 Month Metric Store Cut Off Operator Target Amount 01-Jan-24 Target Sales Store Alabama Green > \$45,000 01-Jan-24 Target Sales Store Alabama Orange > \$40,000 01-Jan-24 Target Sales Store Alabama Red <= \$40,000 01-Feb-24 Target Sales Store Alabama Green > \$50,000 01-Feb-24 Target Sales Store Alabama Orange > \$40,000 01-Feb-24 Target Sales Store Alabama Red <= \$40,000 01-Jan-24 Target Sales Store Boston Green > \$30,000 01-Jan-24 Target Sales Store Boston Orange > \$25,000 01-Jan-24 Target Sales Store Boston Red <= \$25,000 01-Feb-24 Target Sales Store Boston Green > \$100,000 01-Feb-24 Target Sales Store Boston Orange > \$95,000 01-Feb-24 Target Sales Store Boston Red <= \$95,000

The sales table is simple

 Month Metric Store Sales 01-Jan-24 Sales Store Alabama \$   30,000 01-Feb-24 Sales Store Alabama \$   80,000 01-Jan-24 Sales Store Boston \$   27,500 01-Feb-24 Sales Store Boston \$ 120,000

At the end of this, I want to be able to create a measure where I can compare the sales figure to the targets and give a rating. If it is green then I will give it a 2, orange a 1 and red a 0. I can use this as conditional formatting. I'm just not sure how to go about this, is it like an if statement?

Any assistance is appreciated

1 ACCEPTED SOLUTION
Super User

This is a rather tricky one but this might just work.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
3 REPLIES 3
Super User

This is a rather tricky one but this might just work.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper II

You're a wizard. I am looking through your DAX and I still don't know how you managed to do it.

Is it possible to have the operator to not be hard coded in the DAX?

E.g. we monitor lost inventory. So the targets table may have more additional rows like the below. I think applying the rules that you have in the file, it will break?

 Month Metric Store Cut Off Operator Target Amount 01-Jan-24 Lost Inventory Store Alabama Green =< \$5000 01-Jan-24 Lost Inventory Store Alabama Orange < \$7,500 01-Jan-24 Lost Inventory Store Alabama Red > \$10,000
Super User

DAX doesn't have a function similar to INDIRECT in Excel or Expression.Evaluate in M so you'll need to write logical conditions to guide DAX which operator to use.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.