Calculated column or measure depending on parameter

[daso94msg]

Hi everyone,

I have a fairly simple problem, but I'm not really able to solve it.

Let's say I have the following data, Table1. With an ID column and anerr column:

ID,err
1,0.51
2,0.3
3,0.24
4,0.65
5,0.27
6,0.73
7,0.51
8,0.34
9,0.16
10,0.93

Now I want to use a decimal parameter tolerance, that can be set using a slider.

Depending on the value of tolerance, I want to classify the Errors from Table1 into faulty (1) or non-faulty (0) parts and visualize the relative proportions of faulty vs non-faulty in a pie chart.

Conceptionally I just need a calculated column like:

faulty = Table1[err] > tol[tol value]

But as I already found out from other entries in the forum here, that won't work as the calculated column does not update dynamically if the user changes the tolerance value with the slider.

A measure on the other hand does not allow me to use the values from Table1 without aggregation. (I don't really understand the reason?)

I couldn't really believe that it's not possible to solve such a trivial task in PowerBi. 

So here I am asking for your help 🙂

Thanks in advance!

[123abc]

In Power BI, you can achieve your goal by creating a measure that calculates the proportion of faulty vs. non-faulty parts based on the user-defined tolerance parameter. You're correct that calculated columns are static and cannot be used for this purpose. Measures, on the other hand, are designed for dynamic calculations and aggregations, but you can still use them for your scenario.

Here's a step-by-step guide on how to create the measure and visualize the relative proportions of faulty vs. non-faulty parts in a pie chart:

1. Create a Measure: In Power BI, go to the "Model" view, and click on "New Measure" to create a new measure. Name it something like "Faulty Parts Proportion."

2. Define the Measure: Use the following DAX formula to define the measure:

Faulty Parts Proportion =
VAR Tolerance = MAX('Parameter Table'[Tolerance])
VAR FaultyCount = COUNTROWS(FILTER(Table1, Table1[err] > Tolerance))
VAR NonFaultyCount = COUNTROWS(FILTER(Table1, Table1[err] <= Tolerance))
RETURN
DIVIDE(FaultyCount, FaultyCount + NonFaultyCount, 0)

1. In this formula, we use a variable (Tolerance) to capture the user-selected tolerance from a parameter table (you can create a table for this purpose and use a slicer to select the tolerance). Then, we use the FILTER function to count the number of faulty and non-faulty parts based on the tolerance. Finally, we calculate the proportion of faulty parts to the total parts and return it.

2. Create a Pie Chart: In your report view, add a pie chart visualization. Drag the "ID" column to the "Values" field well and drag the "Faulty Parts Proportion" measure to the "Legend" field well.

3. Set Tolerance with a Slider: To allow the user to set the tolerance value using a slider, create a parameter table that contains a single column for "Tolerance." Then, create a slicer visualization based on this parameter table. Users can use the slicer to select the tolerance value dynamically.

Now, when the user adjusts the tolerance value using the slicer, the pie chart will dynamically update to show the relative proportions of faulty vs. non-faulty parts based on the selected tolerance.

This approach combines a measure with a parameter table and a slicer to achieve the dynamic behavior you want.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[dk_dk]

Hi @123abc ,

Power BI does not allow me to add the measure to the Legend field.

[123abc]

You're correct; Power BI does not allow measures to be placed directly in the Legend field of a chart visualization. To achieve the desired result where you can classify the errors into faulty (1) or non-faulty (0) and visualize the proportions in a pie chart, you can follow these steps:

1. Create a new column in your data model that calculates the classification based on the tolerance value. This column will be a calculated column, which is static but is necessary to achieve this specific requirement. You can create it using DAX in Power BI's Data View:

Classification = IF(Table1[err] > [Tolerance], 1, 0)

1. Here, [Tolerance] should be replaced with the parameter or slicer that allows the user to set the tolerance value.

2. Next, create a measure to calculate the count of faulty and non-faulty parts. This measure will use the calculated column you created in the previous step:

Faulty Count = SUMX(Table1, [Classification])
Non-Faulty Count = COUNTROWS(Table1) - [Faulty Count]

1. Finally, create a pie chart visualization. Place the "Faulty Count" measure in the Values field and "Non-Faulty Count" measure in the Values field. You can also add a legend to the chart to distinguish between faulty and non-faulty.

Now, when the user changes the tolerance value using a slicer or parameter, the calculated column will dynamically update based on the tolerance value, and the pie chart will display the relative proportions of faulty vs. non-faulty parts based on the classification.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.