Count values that match criteria

[Anonymous]

Hey guys,

I need some help trying to count how many times that multiple condition occur based upon a distinct count. e.g.

Invoice | Charge Type

123 | Basic

123 | Intermediate

124 | Basic

124 | intermediate

124 | Complex

I would like the count of this to be 1 because there is 1 invoice with all 3 charge types. Im not sure if this makes sense sorry I am only new to this whole thing. Any help is appreciated. Thank you

[Ashish_Mathur]

Hi,

This measure will count the number of invoices where the the Charge Type is =3.  The answer is 1.

Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Invoice]),[Invoice],"ABCD",DISTINCTCOUNT(Data[Charge Type])),[ABCD]=3))

Hope this helps.

[ChandeepChhabra]

@Anonymous 
It would nice if I can see your expected result.

For now you can try this measure. It counts only those invoices which have 3 unique charge types

Invoices with 3 Charges = 
COUNTROWS(
    FILTER(
        VALUES(Table1[Invoice ]),
        CALCULATE(COUNTROWS(VALUES(Table1[Charge Type])))=3
    )
)

Hope it helps

[Anonymous]

That definetly helps thank you.

The only thing that that does is also count blanks. What is the easiest way for it not to count the blank ones

[ChandeepChhabra · Accepted solution]

I am assuming when you say "blanks" you mean the Charge type being blank? Try this measure

Invoices with 3 Charges =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Invoice ] ),
        CALCULATE (
            COUNTROWS (
                CALCULATETABLE (
                    VALUES ( Table1[Charge Type] ),
                    Table1[Charge Type] <> BLANK ()
                )
            )
        ) = 3
    )
)