Use of DAX measure in Matrix visual - how to ignore filters within each cell

[EricCantona]

I'm trying to develop a measure that will analyse, dynamically, the following data table:

Name	Type	Severity	Year
Anna	Cold	1	2020
Anna	Cold	1	2020
Anna	Cold	2	2021
Anna	Cold	1	2021
Brian	Flu	2	2021
Brian	Flu	1	2021
Carol	Cold	3	2020
Carol	Flu	1	2020
Dave	Flu	1	2020
Eric	Cold	1	2020
Eric	Cold	2	2021
Fred	Flu	3	2020

The behaviour I am looking for a matrix table that shows the maximum 'severity' for each 'name' and 'type'. For example  So for example Anna had 4 colds, one with maximum severity 2, so she would count as one in the 'Cold', severity=2 cell, but not in the severity = 1 cell.

I have tried generating a measure to do this as I want the table to be affected by slicers on the report e.g. by selecting individual years. So the expected result would be as follows for all years:

	Max Severity by Name
Type	1	2	3	Total
Cold	0	2	1	3
Flu	2	1	1	4
Total	2	3	2	6

Whereas when selection for year 2020 then you'd get the following:

	Max Severity by Name
	1	2	3	Total
Cold	2	0	1	3
Flu	2	0	1	3
Total	4	0	2	5

The approach I initially tried in the DAX measure is to generate a variable with the Max(Severity) by Name and Type (for which I generate a calculated column 'name_type' which is a concat

enation of the two columns)  and then tried to count a distinctcount on the number of names that match the Max(Severity) for each type. 

Max Severity count = 
VAR MaxSev =
	CALCULATE(
        MAX('events'[Severity]),
        ALLEXCEPT('events','events'[name_type])
)
VAR CurrType =
	CALCULATE(
        MAX('events'[Type]),
        ALLEXCEPT('events','events'[name_type])
)
RETURN
    Max(calculate(
        DISTINCTCOUNT('events'[Name]) ,  FILTER ('events', AND('events'[Severity]=MaxSev,'events'[Type]=CurrType)
    )),0)
​

Which gives me this:

Any ideas on how I can alter the measure to give the expected table? I appreciate I may have to have a second measure with a If Hasonefilter calculation in to get the totals to work, but if I can get the main table working, that would be a start.

[EricCantona · Accepted solution]

I think that is pretty close.
I've not used the summarize function before but it seems to simplify the eventual filtering.

The only issue was that the column and overall totals were not correct. If there was a single 'name' with the same max 'severity' for two different 'types' then it got counted twice. So I altered the code so it does a second summarize based on the 'name' before counting rows. (I also added the max function to report 0 rather than blank).

Max Severity count =
VAR maxtab=
FILTER (
SUMMARIZE ( 'events', 'events'[Type], 'events'[Name], 'events'[Severity] ),
'events'[Severity] = CALCULATE ( MAX ( 'events'[Severity] ), ALL ( 'events'[Severity] ) )
)
return
 MAX(COUNTROWS(SUMMARIZE(maxtab,[Name])),0)

I think this works now.
 

[EricCantona]

Hi Tamerj1
Is there a typo in your formulas? For the definition of VAR T3, you use T3 - should this be T2?

When I tried it with T3 being defined from T2, then I get the following matrix...

Thanks

.

[tamerj1]

@EricCantona 

You are right. Typo mistake and wrong approach as well. Let's try

Max Severity count =
COUNTROWS (
FILTER (
SUMMARIZE ( 'events', 'events'[type], 'events'[name], 'events'[Severity] ),
'events'[Severity] = CALCULATE ( MAX ( 'events'[Severity] ), ALL ( 'events'[Severity] ) )
)
)

[tamerj1]

Hi @EricCantona 

please try

Max Severity count =
VAR T1 =
SUMMARIZE ( 'events', 'events'[name], 'events'[Severity] )
VAR T2 =
CALCULATETABLE (
SUMMARIZE ( 'events', 'events'[name], 'events'[Severity] ),
ALL ( 'events'[Severity] )
)
VAR T3 =
TOPN ( 1, T3, 'events'[Severity] )
VAR T4 =
INTERSECT ( T1, T3 )
VAR Result =
COUNTROWS ( T4 )
RETURN
Result

or

Max Severity count =
SUMX (
SUMMARIZE ( 'events', 'events'[type], 'events'[Severity] ),
VAR T1 =
CALCULATETABLE ( SUMMARIZE ( 'events', 'events'[name], 'events'[Severity] ) )
VAR T2 =
CALCULATETABLE (
SUMMARIZE ( 'events', 'events'[name], 'events'[Severity] ),
ALL ( 'events'[Severity] )
)
VAR T3 =
TOPN ( 1, T3, 'events'[Severity] )
VAR T4 =
INTERSECT ( T1, T3 )
VAR Result =
COUNTROWS ( T4 )
RETURN
Result
)