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] )

)

 

[JEC]

Maybe?

=countrows(summarize('TRT RAW DATA',[Invoice Date],[Name of Branch],[PatientID]))

 

[countryfan_nt]

Maybe?

=countrows(summarize('TRT RAW DATA',[Invoice Date],[Name of Branch],[PatientID]))

Sorry, but that won't work, because I want to distinct count the Invoice Numbers.

 

[JEC]

You can create a table like this and measure based on this new table

Table:

newTable =summarize('TRT RAW DATA', [Invoice Number], [Invoice Date],[Name of Branch],[PatientID])

Measure:

 UniqueNum =distinctcount(newTable[Invoice Number])

 

[RoryA]

provided that the
the invoice dates, name of branches, and patient ID match

Match what? Where are you using the measure?

 

[countryfan_nt]

Match what? Where are you using the measure?

Hi and thank you for your reply, please take a look at the attached image.
please help me with a DAX formula that will act like countifs.

by match I mean if: Invoice Number, InvoiceDate, PatientID are the same distinct count the invoice numbers.

Thank you very much in advance

 

[RoryA]

That doesn't answer my question - where are you using this? As a measure in a pivot table, or a calculated column?

 

[countryfan_nt]

That doesn't answer my question - where are you using this? As a measure in a pivot table, or a calculated column?

calculated column

 

[RoryA]

Why do you want it in a calculated column? How do you intend to use it?

 

[countryfan_nt]

Why do you want it in a calculated column? How do you intend to use it?

I am not an expert, it will be placed in calculated field then to a power pivot. Do please suggest a better way to do it.
btw I came up with a formula, but will be happy to apply an easier option:

=CALCULATE (

COUNTROWS ( T ),

FILTER (

T,

T[InvoiceDate] = EARLIER([InvoiceDate] )&&

T[PatientID] = EARLIER([PatientID] ) &&

T[Name of Branch] = EARLIER([Name of Branch])))