COUNT IF With Group By ?

[Mathais_]

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-111111	12121212	525252
J4-111111	12121212
J4-111111	12121212	925874
JF-333333	13131313	999999

And the Result I would want :

Order	ID	OtherID	NotNullOtherID
J4-111111	12121212	525252	2
J4-111111	12121212		2
J4-111111	12121212	925874	2
JF-333333	13131313	999999	1

Thank you very much.

[Arul]

@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_]

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]

@Mathais_ ,

where is that 0 is coming from?

Thanks,

Arul

[Mathais_]

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-222222	123456		BLANK
KK-99999	987654	555555	1
KK-99999	987654		1
FF-55555	999999		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]

@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