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

Cross Matrix table in Power BI DAX - need to count number of Clients for a specific value

I have the following table, which shows MH(Mental Health type)  -  SA (Substance Abuse type) diagnoses for a particular client:

This table has over 200K records. The data used in this topic (below) is just an example

 

tbl Diagnosis:

 

ProgramID  ClientID  Date         Diagnosis_Desc  Diagnosis_Cat  
666227/1/2018Bi-PolarMH
666228/1/2018AlcoholSA
888236/1/2018CocaineSA
999247/1/2019ScizophreniaMH
999248/1/2019AlcoholSA
222257/1/2020NicotineSA
222258/1/2020PTSDMH
222259/1/2020StressMH

 

Below is the screenshot of the same tbl ( more visible format)

Diagnosis.PNG

 

I also have a calculated column in this table, which calculates number of  Diagnosis categories - SA/MH per client:

So, if I populate my data in Power BI, with [# Category per Client] measure,  it'll look as:

DiagnosisPBI.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).

 

I created 2 calculated tables with the unduplicated MH / SA categories -

zzParam_SA:

SA.PNG

 

zz_Param_MH:

MH.PNG

 

To calculate a Measure for the Cross Matrix I used the following code (adviced by a member of this community):

 

 

Measure = 
VAR _ADDCOLUMN = ADDCOLUMNS('Diagnosis',"Flag",IF([Diagnosis_Desc] = MAX(zzParam_MH[MH]) || 
                             [Diagnosis_Desc] = MAX(zzParam_SA[SA]),1,0))
VAR _SUMMAIZE = 
 SUMMARIZE(_ADDCOLUMN,[ClientWHID],"Sum",               
            SUMX(FILTER(_ADDCOLUMN,'Diagnosis'[ClientWHID] = 
                  EARLIER('DDiagnosis'[ClientWHID])),[Flag]))
RETURN
COUNTAX(FILTER(_SUMMAIZE,[Sum]=2),[ClientWHID])

 

 

 

It works perfectly, but if there is no ProgramID, Date columns in my dataset.

(Only if there is [ClientID], [Diagnosis_Desc], [Diagnosis_Cat], [# Category per Client] columns)

But I need [Date] / [ProgramID] included in this dataset, as well.

I tried to add these 2 columns after the first SUMMARIZE, but it didn't work correctly

(shows incorrect number or cross-paired ClientIDs).

 

I expect to see the following numbers in my matrix (as shown in the picture below):

Alcohol-Bi-Polar = 1 Client (ClientID 22);

Alcohol-Scizophrenia = 1 Client (ClientID 24)

Nicotine-PTSD = 1 Client (ClientID 25)

Nicotine-Stress = 1 Client (ClientID 25)

 

all the other SA-MH combinations have no clients (0), so they're not shown.

 

Cross Matrix.PNG

 

Please help me correctly modify the measure, so it would work as above, but if there is also ProgramID, Date added in my

dataset.

Thanks, as always!

 

 

1 ACCEPTED SOLUTION

here's your starting point:

 

Cross = CROSSJOIN(
SELECTCOLUMNS( CALCULATETABLE(values(Diagnosis[Diagnosis_Desc  ]),Diagnosis[Diagnosis_Cat  ]="MH"),"MH",Diagnosis[Diagnosis_Desc  ]),
SELECTCOLUMNS( CALCULATETABLE(values(Diagnosis[Diagnosis_Desc  ]),Diagnosis[Diagnosis_Cat  ]="SA"),"SA",Diagnosis[Diagnosis_Desc  ]))

 

And then you can add the measure to compute the number of clients that have both. That measure will then honor all your other filters.

Val = countrows(INTERSECT(SELECTCOLUMNS(filter(Diagnosis,Diagnosis[Diagnosis_Desc  ]=SELECTEDVALUE(Cross[MH])),"CI",Diagnosis[ClientID  ]),SELECTCOLUMNS(filter(Diagnosis,Diagnosis[Diagnosis_Desc  ]=SELECTEDVALUE(Cross[SA])),"CI",Diagnosis[ClientID  ])))

 

lbendlin_0-1666018608511.png

As you can see the totals don't work - not sure if that is important for you. If it is then the measure needs to be modified a bit.

View solution in original post

6 REPLIES 6
Hell-1931
Helper II
Helper II

Ibendlin, Thank you It worked!
Also, Distinct () can be added after Countrows () if there multiple Diagnosis_Desc

That way it's calculating unique values!

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

To Ibendlin -

The Diagnosis tbl I pasted in this topic - fully covers my issue and it is sanitized

The outcome I expect to have is also provided in this post!

Since I don't see a responses and since your comment shows problem in this post

I'll delete this one and will try to describe my goal in more obvious way.

I'll also attach the actual table example

Please provide source data in usable format. Screenshots are not a usable format.

Ok, I just did!

Updated the original post adding the same tbl into it

here's your starting point:

 

Cross = CROSSJOIN(
SELECTCOLUMNS( CALCULATETABLE(values(Diagnosis[Diagnosis_Desc  ]),Diagnosis[Diagnosis_Cat  ]="MH"),"MH",Diagnosis[Diagnosis_Desc  ]),
SELECTCOLUMNS( CALCULATETABLE(values(Diagnosis[Diagnosis_Desc  ]),Diagnosis[Diagnosis_Cat  ]="SA"),"SA",Diagnosis[Diagnosis_Desc  ]))

 

And then you can add the measure to compute the number of clients that have both. That measure will then honor all your other filters.

Val = countrows(INTERSECT(SELECTCOLUMNS(filter(Diagnosis,Diagnosis[Diagnosis_Desc  ]=SELECTEDVALUE(Cross[MH])),"CI",Diagnosis[ClientID  ]),SELECTCOLUMNS(filter(Diagnosis,Diagnosis[Diagnosis_Desc  ]=SELECTEDVALUE(Cross[SA])),"CI",Diagnosis[ClientID  ])))

 

lbendlin_0-1666018608511.png

As you can see the totals don't work - not sure if that is important for you. If it is then the measure needs to be modified a bit.

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.

Top Solution Authors