Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
@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 ) )
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 ) )
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.
@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 ) )
yep, works perfectly, thanks a lot for your help
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
14 |
User | Count |
---|---|
44 | |
36 | |
24 | |
24 | |
22 |