Forum Discussion
Filtering Aggregated Table Before Measures are Calculated
I have a table (MainData) with the following structure (one row per student per department). Example dashboard screenshots attached.
| School | Department | Student | Risk |
| East | Marketing | Jim | 2 (Medium) |
| East | History | Sue | 3 (High) |
| East | Marketing | Sue | 1 (Low) |
| West | Marketing | Bob | 1 (Low) |
| West | History | Eric | 1 (Low) |
| Central | Marketing | Mary | 2 (Medium) |
My goal is to create a measure that shows the highest risk, which would be Max([Risk]). I then want to use this measure as a bar chart axis to show how many unique students fall under each risk category, so we would have Medium = 2 students , Low= 2 students, High = 1 student. Student Sue will only be counted once and would fall under "High" because we want the max risk from all her rows. Here's the problem: I am not able to use a measure as the Axis on my bar chart.
Attempt #1: To get around this, first I created the Max([Risk]) measure (this measure is named Max_Risk and shows numeric values 1,2,3). I then create a calculated column (Max_Risk_Final) to say "If Max_Risk = 3 then "High", if 2 then "Medium", if 1 then "Low". This allows me to use the Max_Risk_Final column as my chart axis, and then I can use a distinct count of Student name as my values. I also have a Slicer/filter for Student name. If I set the slicer value to "Sue", the bar chart now shows 2 bars: one for her one high risk value and another for her low risk value. However, I would only want the chart to show 1 bar for Sue with a value of High.
Attempt #2: As an alternate solution, I tried creating an Aggregated table (named AggTable) with DAX that is grouped by Student and shows the max risk level for each. I then joined AggTable to my MainTable on the Student column in the Power BI relationships view. I create a new column called Agg_Max_Risk_Final to get the max risk per student, similar to my first attempt above. When I use the new Agg_Max_Risk_Final column from my AggTable as the chart axis and then set my Slicer = "Sue", I now only get 1 bar coming back showing High risk. This is exactly what I want. But now, let's say I have a different slicer for Department. When I set this slicer = "Marketing", my bar chart should show 4 total students; 2 low and 2 medium risk. Instead, it shows 2 Medium, 1 Low, and 1 High. This is incorrect because no Marketing record shows high risk. Somehow, the High result comes from Sue. While she does have a Low Risk in the Marketing Department, she also has a High risk in the History department. It seems like the Agg_Table is not taking in the MainTable slicer/filter values correctly.
What I'm looking to get out of this: I'd like my slicer/filter to be applied to my MainData table first. Then, the Agg_Table would calculate its measures only based on the newly filtered MainData table. I'd like a flow of Slicer --> MainData -->Agg_Table measures. E.g. If I set department = "Marketing", I want Agg_Table to only take in the Marketing records before processing any measures. I hope this makes sense - hopefully the attached screenshots will provide more clarity. I'm able to expand on anything else needed as well.
Attempt #1:
Attempt #2:
Thank You!
1 Reply
There's no way to do this with calculated tables. Calculated tables are only evaluated during data load and so there is no way for them to react to filters or slicers in the report.
I think you will need to create 3 measures, one each for high, medium and low, which each create an aggregate table on the fly, something like
Num high risk =
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Student]),
"@max risk", CALCULATE( MAX( 'Table'[Risk] ) )
)
return COUNTROWS( FILTER( summaryTable, [@max risk] = 3 ) )
