Counting for 2 Columns

[datapal04]

Hello,

I need to create a measure that will count for 2 columns even though one of the columns is being used as a key for the relationship (many-to-many). Let me explain through an example. 

I have the dimension table below:

Primary Complaint Code	Secondary Complaint Code	Metric
General Announcements  (Safety, Etiquette, & non-Status)	Customer Behaviour	Enforcement of Quiet Zone on upper level of train coaches
Service Change Communication – Content	Service Change Communication – Timeliness/Frequency	Keeping me informed of upcoming schedule changes
General Announcements  (Safety, Etiquette, & non-Status)	Service Status Communication – Timeliness/Frequency	Frequency of announcements on board the train

The Primary Complaint Code column is being used as a key to relate to the fact table (Complaint Code) below:

Case_Number	Complaint Code
301-553535-N9D9V7	Service Status Communication – Timeliness/Frequency
301-553613-H6Y2R5	Customer Behaviour
301-553632-V7R4D1	Customer Behaviour
301-553700-B6R1S4	Service Change Communication – Content
301-553724-G1R7G6	Service Change Communication – Timeliness/Frequency
301-553737-H8C8G9	General Announcements  (Safety, Etiquette, & non-Status)
301-553860-R3C0Y2	Customer Behaviour
301-553967-R9C2N1	Service Change Communication – Content
301-553999-Y7R8G2	Service Change Communication – Content
301-554008-X3C1N4	Customer Behaviour
301-554020-J4G4D2	Customer Behaviour
301-554101-F2Y1T3	General Announcements  (Safety, Etiquette, & non-Status)
301-554214-V2H5W1	Service Status Communication – Timeliness/Frequency
301-554399-J4V0V3	General Announcements  (Safety, Etiquette, & non-Status)

The measure I need to create will need to take the distinct count of the Case_Number for the sum of Primary and Secondary complaint codes for every Metric. The expected result is below:

Metric	Count
Enforcement of Quiet Zone on upper level of train coaches	8
Keeping me informed of upcoming schedule changes	4
Frequency of announcements on board the train	5

Thanks in advance for your help!

[v-cgao-msft · Accepted solution]

Hi @datapal04 ,

My steps are as follows:

1. In the Power Query Editor, select these two columns and unpivot them.

2. Close and apply.

3. New relationship.

4. New measure:

COUNT = COUNT(FactTable[Complaint Code])

5. Result:

6. The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data