cancel
Showing results for
Did you mean:
Impactful Individual

## count the occurrences by category

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

Column/Measure is what i am looking for.

2 ACCEPTED SOLUTIONS
Super User

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Impactful Individual

both the solutions works , but if I have a date filter , it is not filtering to show the count corectly.

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
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Super User

@tejaswidmello , Try a new column like

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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements