Forum Discussion
Calculating values for Cross Matrix table in Power BI DAX
I have the following table, which shows how many MH(Mental Health type) -
SA (Substance Abuse type) diagnosis for a particular client:
Diagnosis:
I also have a measure, which calculates number of Diagnosis categories - SA/MH per client:
So, if I populate my data with [# Category per Client] measure, it'll look as:
My goal is to create a Cross Matrix table, where
X axis = SA diagnosis,
Y axis = MH diagnosis,
value = # of Clients that have a SA-MH diagnosis pair (crossed pair).
This is the measure for my value:
# Dual Diagnosed Clients =
CALCULATE(DISTINCTCOUNT('Diagnosis'[ClientID]),
FILTER('Diagnosis',
'Diagnosis'[# Category per Client] = 2
))
I created 2 tables with the unduplicated MH / SA categories - Attribute_SA [Attrib_1], Attribute_MH[Attrib_2]
My Cross Matrix table, with [# Dual Diagnosed Clients] measure is below:
But, unfortunately, it shows the total # of the unduplicated Dually Diagnosed (SA/MH) clients (ID 22,24,25) instead of showing -
# of the unduplicated Dually Diagnosed (SA/MH) clients for each SA-MH category.
I expect to see the following numbers in my matrix:
Alcohol-Bi-Polar = 1 Client (ClientID 22);
Alcohol-PTSD = 0 Clients (or just an empty cell)
Alcohol-Scizophrenia = 1 Client (ClientID 24)
Alcohol-Stress = 0 Clients
Cocaine-Bi-Polar = 0 Clients
Cocaine has no dually diagnosed clients - all 0 or empty in that row
Nicotine-Bi-Polar = 0 Clients
Nicotine-PTSD = 1 Client
Nicotine-Stress = 1 Client
Please HELP or advice how should I change / update my emasure(s)
Thx in advance!
- Anonymous3 years ago
Hi Hell-1931 ,
Please try this measure.
Measure = VAR _ADDCOLUMN = ADDCOLUMNS('Table',"Flag",IF([Diagnosis_Desc] = MAX(MH[Diagnosis_Desc]) || [Diagnosis_Desc] = MAX(SA[Diagnosis_Desc]),1,0)) VAR _SUMMAIZE = SUMMARIZE(_ADDCOLUMN,[ClientID],"Sum",SUMX(FILTER(_ADDCOLUMN,'Table'[ClientID] = EARLIER('Table'[ClientID])),[Flag])) RETURN COUNTAX(FILTER(_SUMMAIZE,[Sum]=2),[ClientID]) + 0Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- AnonymousNot applicable
Hi Hell-1931 ,
Please try this measure.
Measure = VAR _ADDCOLUMN = ADDCOLUMNS('Table',"Flag",IF([Diagnosis_Desc] = MAX(MH[Diagnosis_Desc]) || [Diagnosis_Desc] = MAX(SA[Diagnosis_Desc]),1,0)) VAR _SUMMAIZE = SUMMARIZE(_ADDCOLUMN,[ClientID],"Sum",SUMX(FILTER(_ADDCOLUMN,'Table'[ClientID] = EARLIER('Table'[ClientID])),[Flag])) RETURN COUNTAX(FILTER(_SUMMAIZE,[Sum]=2),[ClientID]) + 0Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Hell-1931Helper II
Rico Zhou,
Sorry, just to let you know -
Your measure worked perfectly.
But what if I'd needed 2 more fields in my original dataset -
[ProgramID], [Date]?
In that case the measure works not correctly.
How should I modify it, so it would work with [ProgramID], [Date] in a dataset?
Here is the link to my post
Cross Matrix table in Power BI DAX - need to count... - Microsoft Power BI Community
- Hell-1931Helper II
Thank you, I'll try tonight
- Hell-1931Helper II
Thank you so much
I'll try and will accept in 2 days!