Skip to main content
cancel
Showing results for 
Search instead 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

Reply
avidpowerbiuser
Frequent Visitor

Returning a Count Based on Tier 3 in a 3 Tier Hierarchy - Can it be done?

I have a dataset, where the same record identifier can have multiple issues attached to it. As context: The data is obtained from an audit survery. The auditor enters a new record, which is assigned an ID. As they audit, they select an Issue from the Tier 1 Category. Based on this selection, a range of options will appear for selection in the Tier 2 category. Based on the Tier 2 selection, a range of options will appear in the Tier 3 category. They can then repeat the process, and select the exact same selections in Tier 1 and Tier 2 and Tier 3, however, Tier 3 is supposed to be unique. 

 

I would like to return a total count of the record identifier based on the same principles as above. 

I.e.A single record can have the same Tier 1 and Tier 2 categories selected multiple times and they can occur in any combination, but should always have a unique Tier 3 selection. 

 

This means I would have to create an expression that would say:

If the Record ID is duplicated AND

                   if Tier 1 Issue is duplicated AND

                                     if Tier 2 is Duplicated,

                                                              DENSERank Tier 3 issue and remove duplicate (if applicable),

                                                                                   returning a count of 1.

I have pasted a dummy set of values in the table below, and have placed a 1 in the columns where I would be expecting a return count of 1. 

 

I cannot use Power Query to achieve an output, as the dataset is curated. It all has to be completed in Desktop Power BI, using calc column, measure or table etc. 

 

Record IdentifierTier 1 IssueTier 2 IssueTier 3 IssueSUM(IF=1)
10058Quality of clinical careInadequate assessmentCondition or injury was overlooked or wrongly identified1
10058Quality of clinical careInadequate assessmentCondition or injury was overlooked or wrongly identified0
10058Quality of clinical careInadequate assessmentInadequate investigation of symptoms1
10058Quality of clinical careInadequate assessmentInadequate tool used for assessment1
10058Quality of clinical careInadequate treatment/therapyInadequate tool used for assessment1
15891Rights, respect and dignityBreach of confidentialityCareless communication and/or handling of record1
15891Quality of clinical careAbsence of compassionAbsence of compassion1
15891Quality of clinical careInconsiderate service/lack of courtesyAbsence of compassion1
15891Rights, respect and dignityBreach of confidentialityCareless communication and/or handling of record0
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Your sample data is missing an index column (a unique identifier for each row).  Without that it is not clear which row of the duplicate(s) should be marked as 1 and which as 0.

 

If yo don't care about this you can use DISTINCTCOUNT(Record&Tier1&Tier2&Tier3)

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Your sample data is missing an index column (a unique identifier for each row).  Without that it is not clear which row of the duplicate(s) should be marked as 1 and which as 0.

 

If yo don't care about this you can use DISTINCTCOUNT(Record&Tier1&Tier2&Tier3)

This is fantastic, and just what I needed, thanks Ibendlin! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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