how to use calculated measure values for slicer

[jimmyswoosh]

Hi Power BI Community,

I'm in need of some help in trying to slice by the results from a measure.

Goal: to slice by "Outlier" or "Not Outlier". If nothing is selected, displays all.

Steps taken so far:

• Created measures to identify the threshold for each metric.
• Created measures to get each metric's value. <-- this was done because I couldn't directly compare a column to a measure
• Created a measure to compare the metricValue to the threshold. If metricValue > threshold, return "Outlier", otherwise "Not Outlier"
• Created a unrelated table for slicer selection
  • table has two values, "Outlier" and "Not Outlier"
  • created a measure to filter based on slicer selection. <-- not working
    • measure is referenced from: https://community.powerbi.com/t5/Desktop/results-of-a-measure-use-in-a-slicer/m-p/242208/highlight/t...

    • OutlierSelection = 
      VAR Selection = IF(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), 
          LASTNONBLANK(REF_OutlierSlicer[IsOutlier], 0), 
          BLANK()
          )
      RETURN
          IF(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), 
              IF(ISERROR(SEARCH([Comparison], Selection)), 
              0, 
              1), 
          1)

Dataset:

•  EndTime(Pase)
• ID
• MetricName
• MetricValue

Thanks!

-James

[v-yulgu-msft]

Hi @jimmyswoosh,

Thanks for your sharing solution.

Best regards,

Yuliana Gu

[jimmyswoosh]

Hi @v-yulgu-msft,

Thanks for replying. I only got partial solution. I was hoping someone on here would happen to know why the slicers and measures are behaving this way and have an idea to achieve what I need.

Would you happen to know of a way?

-James

[jimmyswoosh · Accepted solution]

I couldn't figure out directly how to solve this issue so I did a workaround.

I created two measures:

• REF_ClassificationProxy
  • This measure checks the filtered value for the outlier slicer. If "Outlier" is selected, it hardcodes "Outlier" to all the returned results and vice versa for "Not Outlier". If the outlier slicer isn't filtered, it'll just use the returned results from the original classification measure.

IF(AND(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), CONTAINS(REF_OutlierSlicer, REF_OutlierSlicer[IsOutlier], "Outlier")),
    "Outlier",
    IF(AND(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), CONTAINS(REF_OutlierSlicer, REF_OutlierSlicer[IsOutlier], "Not Outlier")),
    "Not Outlier",
[REF_Classification]))

• REF_ClassificationProxyNumber
  • Returns "0" for Outlier and "1" for Not Outlier. This is used for conditional formatting in the table.

IF([REF_ClassificationProxy] = "Outlier" , 0, 1)

-James

[jimmyswoosh]

I semi got it working..

I created a measure "OutlierSelection_2" that checks to see if the slicer has one value selected. If it does, is it "Outlier" that's selected? If so, then I would compare metric value to threshold and if metric value > threshold, then return 0 otherwise 1. If the slicer selected value is "Not Outlier", then vice versa, where as if there's no values selected, it will return 0.

OutlierSelection_2 = 
IF(AND(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), CONTAINS(REF_OutlierSlicer, REF_OutlierSlicer[IsOutlier], "Outlier")), 
     	SWITCH(TRUE(),
            [MTTRS_MetricValue] > [MTT_Threshold_mttrs], 0,
            [MTTCA_MetricValue] > [MTT_Threshold_mttca], 0,
            [MTTCN_MetricValue] > [MTT_Threshold_mttcn], 0,
        1), 
        IF(AND(HASONEFILTER(REF_OutlierSlicer[IsOutlier]), CONTAINS(REF_OutlierSlicer, REF_OutlierSlicer[IsOutlier], "Not Outlier")),
            SWITCH(TRUE(),
            [MTTRS_MetricValue] > [MTT_Threshold_mttrs], 1,
            [MTTCA_MetricValue] > [MTT_Threshold_mttca], 1,
            [MTTCN_MetricValue] > [MTT_Threshold_mttcn], 1,
        0),
    0))

I thought setting a visual level filter to the table using "OutlierSelection_2" with the value = 0 would work. It does but it seems that my comparison measure recalculates so although the results are correct, the visuals are not. Not sure if that makes sense but I'll post some photos and include a sample .pbix file.

No filters

This is what the table looks like without filters.

"Not Outlier" filter

This is what the table looks like with "Not Outlier" selected in the slicer. The results are correct however you can see the first record is now flagged as "Outlier" which it is not. I think this is because the Comparison measure recalculates based on the subset data. I don't want that, I want to retain the results from the old calculation.

"Outlier" filter

With "Outlier" selected in the slicer, this is the result. The records shown is correct but again, the classification is wrong. All three records should say "Outlier" in red.

Anyone have any idea how I can retain my "Comparison" measure values after slicing?

Sample .pbix file: https://microsoft-my.sharepoint.com/:u:/p/v-jamech/ETXMeQe0mTtKthJIvPb9J0wBFQMKtBAJlecUalUSQKSEgA?e=...

Thanks!

-James