Sum of count

wem — Fri, 04 Dec 2020 10:19:44 GMT

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.

Re: Sum of count

mahoneypat — Sat, 05 Dec 2020 04:15:33 GMT

Please try a measure like this one

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

Regards,

Pat

wem — Mon, 07 Dec 2020 05:57:20 GMT

Hi Pat,

This solution works excellent! Thanks a lot.

Wim