Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Compare measures to thresholds

Is there a way to create a decent size number of measures (10-20) and then compare them to threshholds?

 

Ideally I would be able to have a table that looks like:

 

kpitargetminmax
measure17010
............

 

 

 

And the join the measure by name on that table to get the value in relation to the thresholds. 

 

 

 

I know that I can brute force it by creating (10-20) x 4 measures each and doing it that way, but that way seems to lie madness.

 

 

Is there a better way? Sample pbix: https://drive.google.com/file/d/1ugeTe8doWs5MuAzSqzxcIFSusTsNAG2_/view?usp=sharing 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output? You could use the measures in the Filters pane for visuals, yes and compare them to numbers, but I'm not sure those can be dynamic. You could create a "flag" measure like in Complex Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

     


    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Just edited the question with sample data, a pbix file, and a picture showing how I am doing it the brute force way. Is there any more context/information that can help clear up my question?

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Data:

     

    KPI:

     

    You may create two calculated column as below.

    Min = 
SWITCH(
    'KPI'[KPI],
    "measure1",MIN(Data[Value1]),
    "measure2",MIN(Data[Value2]),
    "measure3",MIN(Data[Value3]),
    BLANK()
)

Max = 
SWITCH(
    'KPI'[KPI],
    "measure1",MAX(Data[Value1]),
    "measure2",MAX(Data[Value2]),
    "measure3",MAX(Data[Value3]),
    BLANK()
)

     

    Result:

     

    Best Regards

    Allan

     

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