Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have following requirements:
see table below:
if Doc is blank then count-output = 0
but if there is Doc then count the occurrences of BIN based on NO., Type and BiN.
No. | Type | Bin | Doc | count -output |
01 | A | AABB | 1212 | 3 |
01 | A | AABB | 3242 | 3 |
01 | A | AABB | 543 | 3 |
02 | A | CCDD | 2312 | 1 |
03 | A | BBDD | 4343 | 2 |
03 | A | BBDD | 3432 | 2 |
04 | B | AABB | 56523 | 1 |
02 | A | CCDD | 0 |
Appreacite your help!
Column/Measure is what i am looking for.
Solved! Go to Solution.
Count-output CC =
SWITCH (
TRUE (),
'Table'[Doc] = BLANK (), 0,
COUNTROWS (
FILTER (
'Table',
'Table'[Doc] <> BLANK ()
&& 'Table'[No.] = EARLIER ( 'Table'[No.] )
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
&& 'Table'[Bin] = EARLIER ( 'Table'[Bin] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
This calculculated column formula works
=
if(Data[Doc]=BLANK(),0,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[No.]=EARLIER(Data[No.])&&Data[Doc]<>BLANK())))
Hope this helps.
Hi,
This calculculated column formula works
=
if(Data[Doc]=BLANK(),0,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[No.]=EARLIER(Data[No.])&&Data[Doc]<>BLANK())))
Hope this helps.
Hi @Ashish_Mathur and @Jihwan_Kim ,
both the solutions works , but if I have a date filter , it is not filtering to show the count corectly.
Please see below table:
Now if I select date 6/21/2021 and No. 01 , it should bring count 2 and not 3 as on that date there were only 2 counts.
also if I select No. 2 and date is 6/22/2021 then it will show 1 as there is only 1 record
it that something doable?
No. | Type | Bin | Doc | count date | output |
01 | A | AABB | 1212 | 6/21/2021 | 3 |
01 | A | AABB | 3242 | 6/21/2021 | 3 |
01 | A | AABB | 543 | 6/22/2021 | 3 |
02 | A | CCDD | 2312 | 6/22/2021 | 1 |
03 | A | BBDD | 4343 | 6/21/2021 | 2 |
03 | A | BBDD | 3432 | 6/22/2021 | 2 |
01 | B | CCAA | 56523 | 6/21/2021 | 1 |
02 | A | CCDD | 6/21/2021 | 0 |
You wanted a calculated column formula solution. Calculated column formlas do not update when slicer changes are made.
Count-output CC =
SWITCH (
TRUE (),
'Table'[Doc] = BLANK (), 0,
COUNTROWS (
FILTER (
'Table',
'Table'[Doc] <> BLANK ()
&& 'Table'[No.] = EARLIER ( 'Table'[No.] )
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
&& 'Table'[Bin] = EARLIER ( 'Table'[Bin] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Anonymous , Try a new column like
if(isblank([Doc]) , blank(), countx(filter(Table, [Bin] =earlier([Bin]) && not(isblank([Doc]) )), [No.]))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |