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

[avidpowerbiuser]

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 Identifier	Tier 1 Issue	Tier 2 Issue	Tier 3 Issue	SUM(IF=1)
10058	Quality of clinical care	Inadequate assessment	Condition or injury was overlooked or wrongly identified	1
10058	Quality of clinical care	Inadequate assessment	Condition or injury was overlooked or wrongly identified	0
10058	Quality of clinical care	Inadequate assessment	Inadequate investigation of symptoms	1
10058	Quality of clinical care	Inadequate assessment	Inadequate tool used for assessment	1
10058	Quality of clinical care	Inadequate treatment/therapy	Inadequate tool used for assessment	1
15891	Rights, respect and dignity	Breach of confidentiality	Careless communication and/or handling of record	1
15891	Quality of clinical care	Absence of compassion	Absence of compassion	1
15891	Quality of clinical care	Inconsiderate service/lack of courtesy	Absence of compassion	1
15891	Rights, respect and dignity	Breach of confidentiality	Careless communication and/or handling of record	0

[lbendlin · Accepted solution]

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)

[avidpowerbiuser]

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