Row Total for Summary Table Based on Slicers and Filters

FWD_DATAWORKS — Fri, 13 May 2022 21:16:28 GMT

I am trying to find a row total of a summary table that is filtered by slicers.

I am hoping this can be answered without having to provide the actual data.

Here's a sample of the relevant columns:

Student ID	Year	Grade	Test	Mastery Score Group
1234	2016	Grade 3	Test 1	10-19
1234	2016	Grade 3	Test 2	10-19
1234	2016	Grade 3	Test 3	30-39
4567	2016	Grade 3	Test 1	40-49
4567	2016	Grade 3	Test 2	20-29
4567	2016	Grade 3	Test 3	50-59

Note that there are as many as 20 different tests per student per year.

Below are the slicers that are applied:

Here is a summary table visual:

where "count_ID" is the measure

count_ID = CALCULATE(COUNT(erb_clean[ID]))

So row 1 of the summary table visual for student S2196 has 9 instances where he/she scored in either the 0-9, 10-19, 20-29, or 30-39 range.

---------------------------------------------------------------------------------------------------------------------------------------

The "Pattern Filter Boolean" is the measure:

Pattern Filter Boolean =

VAR MinValue = MIN('Pattern Filter'[Pattern Filter])

VAR MaxValue = MAX('Pattern Filter'[Pattern Filter])

VAR CurrentMeasureValue = erb_clean[count_ID]

RETURN

IF(

CurrentMeasureValue >= MinValue && CurrentMeasureValue <= MaxValue,

1, 0

)

-----------------------------------------------------------------------------------------------------------------------------------------

Pattern Filter is just a list of numbers from 0 to 20 that creates a slider:

Pattern Filter =

GENERATESERIES(0, 20)

So anything in the column "count_ID" in the summary table is between 7 and 19 because the table has a filter applied to only show the 1's from the Pattern Filter Boolean measure:

Desired Outcome:

I want to create a measure that counts the number of rows in the summary table now that it has been filtered to only include Pattern Filter Boolean = 1

I want to be able to dispaly this number in a card or make a simple bar graph showing the number.

I thought I could simply accomplish this by summing the Pattern Filter Boolean measure, but I don't know how to sum a measure. I'm hoping there is a simple way to do this, but I cannot figure it out.

Background on what I'm trying to accomplish:

The table right now shows how many times an individual student scored "poorly" on a series of tests (as selected by the slicer: Master Score Group -> showing only score groups of 0-9, 10-19, 20-29, 30-39)

With the Pattern Filter slider set to at least 7, this is showing me all the students who score poorly at least 7 times because anything above 7 triggers a "1" from the Pattern Filter Boolean measure (and the table filters to only include "1")

Each time I change the slider, or each time I change the score groups, I want to know how many students fall into these groups.

Thanks in advance for any help you can provide.

Re: Row Total for Summary Table Based on Slicers and Filters

tamerj1 — Sun, 15 May 2022 13:34:25 GMT

Hi FWD_DATAWORKS

please try

Count Measure = SUMX ( VALUES ( erb_clean[ID] ), [Pattern Filter Boolean] )

topic Row Total for Summary Table Based on Slicers and Filters in DAX Commands and Tips

Row Total for Summary Table Based on Slicers and Filters

Re: Row Total for Summary Table Based on Slicers and Filters