count the occurrences by category

[Anonymous]

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.

@Ashish_Mathur 

[Jihwan_Kim · Accepted 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] )
)
)
)

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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

[Ashish_Mathur]

You wanted a calculated column formula solution.  Calculated column formlas do not update when slicer changes are made.

[Jihwan_Kim · Accepted 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] )
)
)
)

[amitchandak]

@Anonymous , Try a new column like

if(isblank([Doc]) , blank(), countx(filter(Table, [Bin] =earlier([Bin]) && not(isblank([Doc]) )), [No.]))