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