Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 ])))
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.
Ibendlin, Thank you It worked!
Also, Distinct () can be added after Countrows () if there multiple Diagnosis_Desc
That way it's calculating unique values!
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 ])))
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.
User | Count |
---|---|
17 | |
11 | |
5 | |
4 | |
3 |