Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kinkate18nic
Frequent Visitor

Count unique values present across months

Hello,

I have data set similar to below:

Event DateAlertID
1/20/202364hytv57684
1/20/202364hytv57684
2/20/2023hf6474jdd6
2/20/2023hyalam90yd
2/20/2023hyalam90yd
3/20/2023njs7109jdd6
4/20/2023njs7109jdd6
4/20/2023axm86djdkf
4/20/2023axm86djdkf

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

January1
February2
March1
April1

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

1 ACCEPTED 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 ) )

 

 

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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 ) )

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors