Forum Discussion

Mathais_'s avatar
Mathais_
New Member
3 years ago

COUNT IF With Group By ?

Hi, Hello, I have a table with this datas. I would like to add a new column to count the number of IDs per Order for which the OtherID field is not null.   My datas : Order               ID ...
Need Help
Arul's avatar
Arul
Super User
3 years ago

Mathais_ ,

Create a calculated column with this formula,

NotNullOtherID = 
CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        NOT(ISBLANK(Table1[OtherID])) && 
        Table1[Order] = EARLIER(Table1[Order])
    )
)

Thanks,

Arul

Mathais_'s avatar
Mathais_
New Member
3 years ago

A criterea was missing :

Now, how can I force 0 instead of blank value when All Table1[OtherID] values are blank ?

NotNullOtherID = 
CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        NOT(ISBLANK(Table1[OtherID])) && 
        Table1[Order] = EARLIER(Table1[Order]) &&
        Table1[ID] = EARLIER(Table1[ID])
    )
)


 

  • Arul's avatar
    Arul
    Super User
    3 years ago

    Mathais_ ,

    where is that 0 is coming from?

    Thanks,

    Arul

    • Mathais_'s avatar
      Mathais_
      New Member
      3 years ago

      Thank you for your help !

      When Table1[OtherID] is blank, the calculated field show a blank value exemple :

      I want to replace BLANK by 0 

      Order              ID                         OtherID      NotNullOtherID 
      JJ-222222123456 BLANK
      KK-999999876545555551
      KK-99999987654 1
      FF-55555999999 BLANK

       

      I wrote this code but maybe it's not optimized ?

      NotNullOtherID = 
If(
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            NOT(ISBLANK(Table1[OtherID])) && 
            Table1[Order] = EARLIER(Table1[Order]) &&
            Table1[ID] = EARLIER(Table1[ID])
        )
    ) = BLANK(),
    0,
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            NOT(ISBLANK(Table1[OtherID])) && 
            Table1[Order] = EARLIER(Table1[Order]) &&
            Table1[ID] = EARLIER(Table1[ID])
        )
    )
)
      • Arul's avatar
        Arul
        Super User
        3 years ago

        Mathais_ ,

        just add +0 at the end of your measure,

        NotNullOtherID = 
If(
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            NOT(ISBLANK(Table1[OtherID])) && 
            Table1[Order] = EARLIER(Table1[Order]) &&
            Table1[ID] = EARLIER(Table1[ID])
        )
    ) = BLANK(),
    0,
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            NOT(ISBLANK(Table1[OtherID])) && 
            Table1[Order] = EARLIER(Table1[Order]) &&
            Table1[ID] = EARLIER(Table1[ID])
        )
    )
)+0

        Thanks,

        Arul