Summing the Count of 2 Columns

[datapal04]

Hello,

I am trying to create a measure that sums the count of 2 columns even though one of the columns is used as a primary key in a relationship to a fact table. 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 used as a key to relate to the fact table below (Complaint Code😞

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 sum the discount count of case numbers for both 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!

[ryan_mayu · Accepted solution]

@datapal04 

pls try this

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Complaint Code]=max('Dimension'[Primary Complaint Code])))+CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Complaint Code]=max('Dimension'[Secondary Complaint Code])))

pls see the attachment below