DAX formula behaving like countifs.

[countryfan_nt]

Hello friends, Hope all is well!

please help me fix the following DAX formula. I want to distinct count the Invoice Numbers, provided that the
the invoice dates, name of branches, and patient ID match.

the formula below is not working; I am getting the following error:


Thank you very much in advance!

=CALCULATE (

    DISTINCTCOUNT( 'TRT RAW DATA'[Invoice Number] ),

    'TRT RAW DATA'[Invoice Date] = VALUES ( 'TRT RAW DATA'[Invoice Date] ) &&

    'TRT RAW DATA'[Name of Branch] = VALUES ( 'TRT RAW DATA'[Name of Branch] ) &&

    'TRT RAW DATA'[PatientID] = VALUES ( 'TRT RAW DATA'[PatientID] )

)

 

[RoryA]

If you put it in a calculated column, you will get the same distinct count for every row that matches those three items. That doesn't make sense to me for a calculation you are going to use in a pivot table as it will be duplicated.
So, how do you plan to use it in a pivot table? If you are going to have the Invoice Date, patient ID and Branch in the pivot, then all you actually need is a distinct count of the invoice number. If not, then you probably need to summarize by the 4 columns, or create a key column that concatenates those 4 columns, then use a countrows statement in a measure.