Forum Discussion
kinkate18nic
3 years agoFrequent Visitor
Count unique values present across months
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/2...
- 3 years ago
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 ) )
tamerj1
Community Champion
3 years agokinkate18nic
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 ) )
kinkate18nic
3 years agoFrequent Visitor
yep, works perfectly, thanks a lot for your help