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

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

Hell-1931 — Mon, 17 Oct 2022 02:07:29 GMT

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
666	22	7/1/2018	Bi-Polar	MH
666	22	8/1/2018	Alcohol	SA
888	23	6/1/2018	Cocaine	SA
999	24	7/1/2019	Scizophrenia	MH
999	24	8/1/2019	Alcohol	SA
222	25	7/1/2020	Nicotine	SA
222	25	8/1/2020	PTSD	MH
222	25	9/1/2020	Stress	MH

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

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:

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:

zz_Param_MH:

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.

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!

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

lbendlin — Sun, 16 Oct 2022 22:22:08 GMT

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-p/963216
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

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

Hell-1931 — Sun, 16 Oct 2022 22:57:09 GMT

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

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

lbendlin — Sun, 16 Oct 2022 23:16:56 GMT

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

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

Hell-1931 — Mon, 17 Oct 2022 02:08:12 GMT

Ok, I just did!

Updated the original post adding the same tbl into it

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

lbendlin — Mon, 17 Oct 2022 14:57:30 GMT

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

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.

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

Hell-1931 — Tue, 18 Oct 2022 06:45:52 GMT

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

That way it's calculating unique values!