How to count the Distinct count of ID's for each CategoryID whose status is s

[Naresh91]

Hi 

ID	CategoryID	Status	FlagStatus	Date	Output Count
a123	1	1	1	4/1/2021
a123	2	1	1	4/1/2021	1
a123	3	1	1	4/1/2021	1
b234	1	0	1	4/3/2021
b234	2	1	1	4/3/2021	1
a123	1	1	0	4/3/2021
c123	2		1	4/2/2021	1

from the above table need to count of ID for each  CategoryID whose status is either "blank" and "1" and FlagStatus is "1" for the Latest Date
"OutputCOunt" is the required output

Eg: for ID = "a123" have the 3 CategoryID's {1,2,3} from that Category ID's 2, 3 have the Flagstatus as 1 for latest Date but for CategoryID 1 have the FlagStatus as 0 for latest Date so it should not be counted.

[Jihwan_Kim]

Hi, @Naresh91 

Please try the below measure.

Output =
VAR currentcategory =
MAX ( 'Table'[CategoryID] )
VAR grouptablefindmaxdate =
MAXX (
GROUPBY (
FILTER ( ALL ( 'Table' ), 'Table'[CategoryID] = currentcategory ),
'Table'[CategoryID],
"@maxdate", MAXX ( CURRENTGROUP (), 'Table'[Date] )
),
[@maxdate]
)
VAR filtertable =
FILTER (
ALL ( 'Table' ),
'Table'[CategoryID] = currentcategory
&& 'Table'[Date] = grouptablefindmaxdate
&& 'Table'[Status] <> 0
&& 'Table'[FlagStatus] = 1
)
RETURN
COUNTROWS ( filtertable )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/