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                         OtherID          
J4-11111112121212525252
J4-11111112121212 
J4-11111112121212925874
JF-33333313131313999999

 

And the Result I would want :

Order                ID            OtherID  NotNullOtherID  
J4-11111112121212  525252  2
J4-11111112121212 2
J4-111111121212129258742
JF-333333131313139999991

 

Thank you very much.

Need Help

5 Replies

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