Forum Discussion

Ben81's avatar
Ben81
Helper I
3 years ago

Summarize with distinct count

Hi, 
I have created a measure to do a count of ID for the last time it was modified but I later realised that I need this count to be a distinct count of the ID and I'm struggling with the DAX.  Can anyone help?

Measure =
COUNTX (
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "MaxDate", MAX ( 'Table'[LASTMODIFIEDDATE] )
    ),
    [MaxDate]
)

Thanks,
Ben
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Ben81 Try:

    Measure =
COUNTROWS
  DISTINCT(
    SELECTCOLUMNS(
      SUMMARIZE (
        'Table',
        'Table'[ID],
        "MaxDate", MAX ( 'Table'[LASTMODIFIEDDATE] )
      ),
      "__ID",[ID]
    )
  )
)
    • Ben81's avatar
      Ben81
      Helper I
      3 years ago

      Thanks mate that worked a treat but can I push my luck a bit more and ask how would that work if I wanted to drop in a USERELATIONSHIP? 

      I need to create another measure identical to the above but based on another date field within the same table and both measures are controlled via date slicer.

      Thanks