cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## The power bi does not calculate identities in different rows

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

2 ACCEPTED SOLUTIONS
Super User

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.

Super User

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:

2 REPLIES 2
Super User

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:

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors