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

 

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

4 Replies

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Icon for Impactful Individual rankImpactful 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
        Icon for Impactful Individual rankImpactful 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
    )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.