Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count values that match criteria

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...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    7 years ago

    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
    )
)
ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    ChandeepChhabra
    Impactful Individual
    7 years ago

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