Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hell-1931
Helper II
Helper II

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:

Diagnosis.PNG

 

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:

Cat_Per_Clnt_PBI.PNG

 

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:

CrossMatrix.PNG

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!

 

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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]) + 0

Result is as below.

RicoZhou_1-1664527492885.png

 

 

Best Regards,
Rico Zhou

 

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

View solution in original post

4 REPLIES 4
Hell-1931
Helper II
Helper II

Thank you so much
I'll try and will accept in 2 days!

Hell-1931
Helper II
Helper II

Thank you, I'll try tonight

v-rzhou-msft
Community Support
Community Support

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]) + 0

Result is as below.

RicoZhou_1-1664527492885.png

 

 

Best Regards,
Rico Zhou

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.