Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Power BI Community!
Goal: To be able to identify outlier values from the dataset dynamically and have them highlighted in the table.
Approach:
Problem: When I slice the data, the measure returns the correct outlier threshold. However the 'REF_IsOutlier?' column doesn't seem to recognize the newly updated value returned from measure and instead is calculating based on the outlier value that's calculated for the whole dataset instead of the subset of it.
Research: I've spent quite a bit of time on the Power BI forums researching and also Googling in general to see what might the issue. It seems the consensus is that tables and calculated columns are only "computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report(like a Slicer)."
Source: https://community.powerbi.com/t5/Desktop/New-table-with-measures-values/td-p/189124
I have a dataset 'FUN_MTT_measures' that has the following fields:
Created calculated measure 'MTTCA_UpperFence_TEST' <-- This works
MTTCA_UpperFence_TEST = // Calculating the upper boundary for MTTCA // Data used from FUN_MTT_measures VAR MTTCA_UpperQuartile = CALCULATE(PERCENTILE.INC(FUN_MTT_measures[MetricValue], 0.75), ALLSELECTED(FUN_MTT_measures), FUN_MTT_measures[MetricName] = "mttca") // Calculating upper quartile VAR MTTCA_LowerQuartile = CALCULATE(PERCENTILE.INC(FUN_MTT_measures[MetricValue], 0.25), ALLSELECTED(FUN_MTT_measures), FUN_MTT_measures[MetricName] = "mttca") // Calculating lower quartile VAR MTTCA_IQR = MTTCA_UpperQuartile - MTTCA_LowerQuartile // Calculating inter quartile RETURN (MTTCA_UpperQuartile + 1.5 * MTTCA_IQR) // Return upper boundary for MTTCA
Created calculated column 'REF_IsOutlier' in the 'FUN_MTT_measures' tables <-- This doesn't work
REF_IsOutlier = SWITCH(TRUE(), // [Calculated Column] Returns either "Outlier" or "Not Outlier" depending on the current Metric Value compared to the corresponding upper boundary of the selected metric. This field can be used to filter the table between outlier and non-outlier data. AND(FUN_MTT_measures[MetricName] = "mttrs", FUN_MTT_measures[MetricValue] > [MTTRS_UpperFence_TEST]), "Outlier", // Checks to see if MTTRS' metric value is an outlier. If yes, returns "Outlier" text. AND(FUN_MTT_measures[MetricName] = "mttca", FUN_MTT_measures[MetricValue] > [MTTCA_UpperFence_TEST]), "Outlier", // Checks to see if MTTCA's metric value is an outlier. If yes, returns "Outlier" text. AND(FUN_MTT_measures[MetricName] = "mttcn", FUN_MTT_measures[MetricValue] > [MTTCN_UpperFence_TEST]), "Outlier", // Checks to see if MTTCN's metric value is an outlier. If yes, returns "Outlier" text. "Not Outlier") // Not an outlier, returns "Not Outlier" text.
I've attempted to do a simple test with an if function but that doesn't seem to work either..
REF_IsOutlier = IF(AND(FUN_MTT_measures[MetricName]="mttca", FUN_MTT_measures[MetricValue] > [MTTCA_UpperFence_TEST]), "Outlier", "Not Outlier")
I was hoping one of you gurus here can provide me some direction or an alternative approach.
Thanks in advance!
-James
Solved! Go to Solution.
I was able to accomplish what I needed by creating a measure for each metric value and a measure for each metric's outlier threshold.
Then I created a measure, using switch to compare the metric value to the threshold and return either "Outlier" if it's above the threshold or "Not Outlier" if it's below or equal to the threshold.
hi, @jimmyswoosh
Calculate table and calculate column all affected by any slicer or other visuals on the report. they are only affected by
the database refresh. So you can't compare measure with column.
The only way is that you create a measure again and then compare them.
Best Regards,
Lin
Thanks @v-lili6-msft for the reply,
For clarification on my part, are you saying that there's no way to acheive a workaround solution since we can't compare measure in a column?
-James
hi, @jimmyswoosh
the result of measure is dynamic and the result of column is static, so they can't be compared in together.
It is usually that column and column comparisons or measure and measure comparisons.
Best Regards,
Lin
Thanks for the reply @v-lili6-msft, I'm aware of the limitation of unable to compare measure to column. I'm trying to see if there's an alternative approach, not using the measure to column comparison to still achieve what I need.
Hi @Greg_Deckler,
Sorry for the sudden loop in. I was reading through one of your posts on dynamic ABC classification (https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146). I thought my issue here is pretty similar and wanted to get your take on it.
I have a dataset of values, I created measures to dynamically calculate outlier threshold aka. upper boundary. Now I want to add a column to the table where it says "Outlier" or "Not Outlier" and dynamically change as we slice by different attributes. Issue is that we can't do a column to measure comparison.
Looking at your solution to Dynamic ABC Classification, I was wondering if we could do something similar:
Edit [10/25/2018]
Looking at @Greg_Deckler's DAX some more and I realized that the values you used are hard coded in and not dynamic since you're comparing "[__CumulatedPercentage]" to 0.7 and 0.9. Aside from that, do you have any ideas how we can still achieve what I need?
mABC Class = VAR __salesTable = ADDCOLUMNS(ALLSELECTED('Sales SalesOrderDetail'),"__TotalSale",[OrderQty]*[UnitPrice]) VAR __salesTable1 = GROUPBY(__salesTable,[ProductID],"__ProductSales",SUMX(CURRENTGROUP(),[__TotalSale])) VAR __salesTable2 = ADDCOLUMNS(__salesTable1,"__CumulatedSales",SUMX(FILTER(__salesTable1,[__ProductSales]>=EARLIER([__ProductSales])),[__ProductSales])) VAR __totalProductSales = SUMX(__salesTable1,[__ProductSales]) VAR __salesTable3 = ADDCOLUMNS(__salesTable2,"__CumulatedPercentage",DIVIDE([__CumulatedSales],__totalProductSales,0)) VAR __salesTable4 = ADDCOLUMNS(__salesTable3,"__ABC Class",SWITCH(TRUE(),[__CumulatedPercentage]<=0.7,"A",[__CumulatedPercentage]<=0.9,"B","C")) VAR __salesTable5 = FILTER(__salesTable4,[ProductID] = MAX('Production Product'[ProductID])) RETURN MAXX(__salesTable5,[__ABC Class])
-James
I was able to accomplish what I needed by creating a measure for each metric value and a measure for each metric's outlier threshold.
Then I created a measure, using switch to compare the metric value to the threshold and return either "Outlier" if it's above the threshold or "Not Outlier" if it's below or equal to the threshold.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |