topic Re: Count unique values present across months in DAX Commands and Tips

Count unique values present across months

kinkate18nic — Thu, 01 Jun 2023 18:34:08 GMT

Hello,

I have data set similar to below:

Event Date	AlertID
1/20/2023	64hytv57684
1/20/2023	64hytv57684
2/20/2023	hf6474jdd6
2/20/2023	hyalam90yd
2/20/2023	hyalam90yd
3/20/2023	njs7109jdd6
4/20/2023	njs7109jdd6
4/20/2023	axm86djdkf
4/20/2023	axm86djdkf

I want to count unique alertIDs month wise.

the measure : UniqueAlertIDCount = DISTINCTCOUNT('TableName'[AlertID])
counts alertID twice if its present in two months, in this case "njs7109jdd6" is present in both March and April, the out put i want is

Month Unique AlertID Count

January	1
February	2
March	1
April	1

However alerting and modify measures, I still get: April as 2

Re: Count unique values present across months

tamerj1 — Thu, 01 Jun 2023 19:11:41 GMT

Hi kinkate18nic

Please try

Unique AlertID Count =
VAR CurrentDate =
MIN ( 'Table'[Event Date] )
VAR CurrentIDs =
VALUES ( 'Table'[AlertID] )
VAR PreviousIDs =
DISTINCT (
SELECTCOLUMNS (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Event Date] < CurrentDate ),
"AlertID", 'Table'[AlertID]
)
)
RETURN
COUNTROWS ( EXCEPT ( CurrentIDs, PreviousIDs ) )

Re: Count unique values present across months

kinkate18nic — Fri, 02 Jun 2023 06:35:47 GMT

Thank for the measure, this seems to be working fine. Just a hiccup, suppose if there is a blank alertID in a month, will that cause the count of the month to increase? I was testing this out in my dataset, for march i have actual 4 unique alertids and there is one entry with eventid but alert id blank. for march this measure is providing 5 as the count.

Re: Count unique values present across months

tamerj1 — Fri, 02 Jun 2023 08:27:53 GMT

kinkate18nic
Yes, it will be counted as 1. But you can filter it out using either of the folowing depending on whether the blank is acually BLANK () or just empty string ""

Unique AlertID Count = VAR CurrentDate = MIN ( 'Table'[Event Date] ) VAR CurrentIDs = FILTER ( VALUES ( 'Table'[AlertID] ), 'Table'[AlertID] <> BLANK () ) VAR PreviousIDs = DISTINCT ( SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Event Date] < CurrentDate ), "AlertID", 'Table'[AlertID] ) ) RETURN COUNTROWS ( EXCEPT ( CurrentIDs, PreviousIDs ) ) Unique AlertID Count = VAR CurrentDate = MIN ( 'Table'[Event Date] ) VAR CurrentIDs = FILTER ( VALUES ( 'Table'[AlertID] ), 'Table'[AlertID] <> "" ) VAR PreviousIDs = DISTINCT ( SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Event Date] < CurrentDate ), "AlertID", 'Table'[AlertID] ) ) RETURN COUNTROWS ( EXCEPT ( CurrentIDs, PreviousIDs ) )

Re: Count unique values present across months

kinkate18nic — Fri, 02 Jun 2023 17:36:28 GMT

yep, works perfectly, thanks a lot for your help