Calculate repeated occurrences in a column with applied date filters

[tomekm]

Hi,

I'm trying to get a formula that will provide the number of repeated rows in a column, given a filter applied (in my case, it's a period filter). The table looks like the below (period filter in this case is selecting only Aug-2021 and Sep-2021). The last column is the desired output.

Thank you.

Period	Concat (master)	Count of Concat (master)
Aug-2021	A	2
Sep-2021	A	2
Aug-2021	B	2
Sep-2021	B	2
Aug-2021	C	1
Sep-2021	D	1
Aug-2021	E	1
Sep-2021	F	1

[Anonymous · Accepted solution]

Hi @tomekm ,

Try this

Count of Concat (master) =
CALCULATE (
    COUNT ( 'Table'[Concat (master)] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Concat (master)] = MAX ( 'Table'[Concat (master)] )
    )
)

With the ALLSELECTED function, your results will still be affected by the filter.

If you want the results not to be affected by the filter, you can modify it to

Count of Concat (master) =
CALCULATE (
    COUNT ( 'Table'[Concat (master)] ),
    ALLEXCEPT ( 'Table', 'Table'[Concat (master)] )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@tomekm Depending on exactly what you want, this could be something simple like:

Count column =
  VAR __Concat = [Concat (master)]
RETURN
  COUNTROWS(FILTER('Table',[Concat (master)] = __Concat))

Or something complex like a variation on Cthulhu: Cthulhu - Microsoft Power BI Community

[tomekm]

Hey Greg,

Please see attached. I pasted your formula in my pbi file (last column here), and what I'm getting is the total count of ocurrences but for all the Periods, even though I have selected only 2021-08 and 2021-09 in my filter. Ideally, it would show only a count of 2 for all these rows, since in this case all the Concat (master) values are present both in Aug to Sept.

Thanks.