Sum of count

[wem]

Hi,

Below a simplified view of the model I use for my calculations.

I've made a measure (count) of FileNumber. In that table the complaint_ID and FileNumber are unique.

Count FileNumber generates: 1000 unique FileNumbers

In order to count FileNumbers on Department (Table 2) and on Subject (Table 3) I made following dax:

Calculate ( measureFileNumber, crossfilter ( Table1[complaint_ID], Table2[complaint_ID], 2), crossfilter ( Table2[cyclus_ID], Table3[Cyclus_ID],2 ) ) 

So far so good because it 'works': it's returning  a unique measureFileNumber on level of Department and Subject.

But in reality one FileNumber can affect more then one Department or more then one Subject.

example:

FileNumber 2018-800 relates to 2 department and relates to 2 Subjects.

2018-800 / Department 1 / Subject 1

2018-800 / Department 2 / Subject 1

2018-800 / Department 2 / Subject 2

In this case, the MeasureFileNumber should return 3 instead of 1 when displayed in a Card-visual

I've been trying with sumx (solution I found on the community), combined with the different sorts of 'counts' but without the expected result.

Can someone please assist me in finding the correct dax formula?

Thanks in advance.

[mahoneypat · Accepted solution]

Please try a measure like this one

Subject Count = COUNTROWS(SUMMARIZE(Table3, Table3[Subject], Table2[Complaint_ID], Table2[Department]))

Regards,

Pat

[wem]

Hi Pat, 

This solution works excellent! Thanks a lot.

Wim