- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Alternative Approaches To Referencing Calculated M...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

jimmyswoosh

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2018
04:09 PM

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 "

__ 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:

- 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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

jimmyswoosh

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018
12:13 PM

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

v-lili6-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2018
10:22 PM

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.

If this post

jimmyswoosh

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2018
09:43 AM

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

v-lili6-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2018
02:46 AM

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.

If this post

jimmyswoosh

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2018
12:56 PM

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:

- Create a measure
- Create variables to create a copy of the main table, add column [IsOutlier] and do the comparison in there
- Use a switch case function to either return "Outlier" or "Not Outlier" to the column [IsOutlier]
- Return the table
- 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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018
12:13 PM

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.