cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jimmyswoosh Regular Visitor
Regular Visitor

Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

Hi Power BI Community!

 

Goal: To be able to identify outlier values from the dataset dynamically and have them highlighted in the table.

 

Approach:

  • I have created a calculated measure 'MTTCA_UpperFence_TEST' for a specific metric that dynamically calculates the outlier threshold and returns that value. <-- This is working
  • Created a calculated column in the 'FUN_MTT_measures' table called 'REF_IsOutlier?' This column would have a string value of either 'Outlier' or 'Not Outlier' based on the calculation of is the MetricValue > MTTCA_UpperFence_TEST. <-- Not Working

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)."

 

Sourcehttps://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:

  • Date : Date
  • LocalID : Int
  • AreaPathExtractDAD: String
  • MetricName : String
  • MetricValue :  Long

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
jimmyswoosh Regular Visitor
Regular Visitor

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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.

5 REPLIES 5
Community Support Team
Community Support Team

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jimmyswoosh Regular Visitor
Regular Visitor

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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

Community Support Team
Community Support Team

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jimmyswoosh Regular Visitor
Regular Visitor

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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:

  1. Create a measure
  2. Create variables to create a copy of the main table, add column [IsOutlier] and do the comparison in there
  3. Use a switch case function to either return "Outlier" or "Not Outlier" to the column [IsOutlier]
  4. Return the table
  5. Plot the measure in the "Values" field in a table visualization.

 

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

jimmyswoosh Regular Visitor
Regular Visitor

Re: Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

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.