Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
hi everyone
this is the my table
ID isDiagnosisDiabetes isDiagnosisHeartFailure isDiagnosisHypertension Date
1 Yes No No 1/1/2022
1 No No Yes 2/7/2022
i would like to Count any patient with more than one diseases
but power bi in this case will return 0 id
when should return 1
becuse patient number 1 has Diabetes and hypertension (more than one diseases ) but becuse it happend in different dates (rows)
power bi will not count it !
how can i Solve this problem?
THANK YOU
Solved! Go to Solution.
Hi @hana123
See it all at work in the attached file.
Best to unpivot the columns to get a more manageable table structure first.
Then you can create a measure for the number of patients:
MoreThan1DiseaseFlag =
VAR aux_ =
CALCULATE ( DISTINCTCOUNT ( Table1[Diagnosis] ), Table1[Result] = "Yes" ) > 1
RETURN
IF ( aux_, 1, 0 )
that uses this other measure:
NumPatientsWithMoreThan1Disease =
SUMX ( DISTINCT ( Table1[ID] ), [MoreThan1DiseaseFlag] )
You can also use [MoreThan1DiseaseFlag] to show in a table visual the list of patients with more than one disease, by using the measure as a table visual and selecting it to show when the value is 1
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
hi @hana123
you may create a measure like this:
PatientsWithMultiDisease =
SUMX(
VALUES(data[ID]),
VAR _db =
CALCULATE(COUNTROWS(data), data[IsDB]="Yes")
VAR _hf =
CALCULATE(COUNTROWS(data), data[IsHF]="Yes")
VAR _ht =
CALCULATE(COUNTROWS(data), data[IsHT]="Yes")
RETURN
IF(_db+_hf+_ht>1, 1, 0)
)
i tried to expand your data sample:
and verified the measure with a card visual:
hi @hana123
you may create a measure like this:
PatientsWithMultiDisease =
SUMX(
VALUES(data[ID]),
VAR _db =
CALCULATE(COUNTROWS(data), data[IsDB]="Yes")
VAR _hf =
CALCULATE(COUNTROWS(data), data[IsHF]="Yes")
VAR _ht =
CALCULATE(COUNTROWS(data), data[IsHT]="Yes")
RETURN
IF(_db+_hf+_ht>1, 1, 0)
)
i tried to expand your data sample:
and verified the measure with a card visual:
Hi @hana123
See it all at work in the attached file.
Best to unpivot the columns to get a more manageable table structure first.
Then you can create a measure for the number of patients:
MoreThan1DiseaseFlag =
VAR aux_ =
CALCULATE ( DISTINCTCOUNT ( Table1[Diagnosis] ), Table1[Result] = "Yes" ) > 1
RETURN
IF ( aux_, 1, 0 )
that uses this other measure:
NumPatientsWithMoreThan1Disease =
SUMX ( DISTINCT ( Table1[ID] ), [MoreThan1DiseaseFlag] )
You can also use [MoreThan1DiseaseFlag] to show in a table visual the list of patients with more than one disease, by using the measure as a table visual and selecting it to show when the value is 1
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |