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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - July 2024

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

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors