Forum Discussion

Kate_McCulloch's avatar
Kate_McCulloch
Regular Visitor
1 year ago
Solved

Count unique Values in a month

I'm trying to count how many contacts a person has had in a month, the end column is what I would like it to calculate but struggling to find a measure to do this, can anyone help. 

 

 

 

Thank you 

 

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Kate_McCulloch , Try using

     

    Contacts in Month =
    CALCULATE(
    COUNT('Table'[Contact Date]),
    FILTER(
    'Table',
    'Table'[UNIQUE ID] = EARLIER('Table'[UNIQUE ID]) &&
    MONTH('Table'[Contact Date]) = MONTH(EARLIER('Table'[Contact Date])) &&
    YEAR('Table'[Contact Date]) = YEAR(EARLIER('Table'[Contact Date]))
    )
    )

9 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Kate_McCulloch , Try using

     

    Contacts in Month =
    CALCULATE(
    COUNT('Table'[Contact Date]),
    FILTER(
    'Table',
    'Table'[UNIQUE ID] = EARLIER('Table'[UNIQUE ID]) &&
    MONTH('Table'[Contact Date]) = MONTH(EARLIER('Table'[Contact Date])) &&
    YEAR('Table'[Contact Date]) = YEAR(EARLIER('Table'[Contact Date]))
    )
    )

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    Hi Kate_McCulloch 

     

    The following dax measure should work

    Contacts in Month =
    VAR CurrentPerson = SELECTEDVALUE('Table'[UNIQUE ID])
    VAR CurrentMonth = MONTH(SELECTEDVALUE('Table'[Contact Date]))
    VAR CurrentYear = YEAR(SELECTEDVALUE('Table'[Contact Date]))
    RETURN
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            ALL('Table'),
            'Table'[UNIQUE ID] = CurrentPerson &&
            MONTH('Table'[Contact Date]) = CurrentMonth &&
            YEAR('Table'[Contact Date]) = CurrentYear
        )
    )
     

     

    (I modified a little bit your sample 🙂 )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Kate_McCulloch,

    Thanks for reaching out to the Microsoft fabric community forum. It seems you want to calculate for each row (person + contact date), the count of how many contacts that same person had in that same calendar month (and year). As Cookistador has responded to your query with a measure, kindly go through his response and check if your issue can be resolved.

     

    I would also take a moment to thank Cookistador and bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Kate_McCulloch,

      As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

      If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

       

      Best Regards,

      Hammad.

      • Kate_McCulloch's avatar
        Kate_McCulloch
        Regular Visitor
        1 year ago

        Hi Apologies, we had alot of system issues last week, the solution from bhaunu_gautam worked to a point but was having issues with a couple not calculating correctly. I wasn't able to further test anything due to the issues last week

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Here's another way.  Write this measure

    Measure = COUNTROWS(Data)
    Write this visual calculation
    Sum = CALCULATE([Measure],COLLAPSE(ROWS))