Skip to main content
cancel
Showing results for 
Search instead 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

Reply
alee5210
Helper II
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
danextian
Super User
Super User

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

danextian_0-1717056264756.png

 

 










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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

danextian_0-1717056264756.png

 

 










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

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.










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors