Forum Discussion

kinkate18nic's avatar
kinkate18nic
Frequent Visitor
3 years ago
Solved

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...
"DAX"
"MEASURE"
  • tamerj1's avatar
    tamerj1
    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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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

kinkate18nic's avatar
kinkate18nic
Frequent Visitor
3 years ago

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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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 ) )

     

     

    • kinkate18nic's avatar
      kinkate18nic
      Frequent Visitor
      3 years ago

      yep, works perfectly, thanks a lot for your help