Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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])
)
)
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])
)
)
)
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
User | Count |
---|---|
91 | |
77 | |
71 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
75 | |
61 | |
58 |