Forum Discussion

lnik's avatar
lnik
Frequent Visitor
2 years ago
Solved

Distinct count by max date

Hi everyone,

I am trying to calculate distinct values based on max date column.

 

But with measure 

DistinctCount = calculate(DISTINCTCOUNT(Table1[Item]),Table1[Date Visit]=MAX(Table1[Date Visit]))
it count also Item 3 although he is not with max date.
Thank you!
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,lnik 

    I am glad to help you. 

    According to your description, you want to distinct count by max date? 

    If I understand you correctly, then you can refer to my solution. 

     

    1. You can start by creating a new table. 

     

     

    Table 2 = 
VAR _distinct =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        'Table'[Customer],
        'Table'[Date Visit],
        "MaxDate",
            CALCULATE (
                MAX ( 'Table'[Date Visit] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) )
            )
    )
RETURN
    FILTER (
        _distinct,
        IF ( 'Table'[Item] = 3, BLANK (), 'Table'[Date Visit] = [MaxDate] )
    )

     

    1. Create a Measure for calculating the number of rows corresponding to each Item, and you'll end up with the result you want. 

     

    DistinctCount = 
VAR _currentItem =
    MAX ( 'Table 2'[Item] )
RETURN
    COUNTROWS ( FILTER ( ALL ( 'Table 2'[Item] ), 'Table 2'[Item] = _currentItem ) )

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello friend, 
    I have tried the same formula you mentioned...its working....sharing the snaps

    Please let me know the problem you are facing.

    • lnik's avatar
      lnik
      Frequent Visitor
      2 years ago

      I want to use ut in matrix visualisation but there it shows 1 against item 3 and I expect it to be empty

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,lnik 

    I am glad to help you. 

    According to your description, you want to distinct count by max date? 

    If I understand you correctly, then you can refer to my solution. 

     

    1. You can start by creating a new table. 

     

     

    Table 2 = 
VAR _distinct =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        'Table'[Customer],
        'Table'[Date Visit],
        "MaxDate",
            CALCULATE (
                MAX ( 'Table'[Date Visit] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) )
            )
    )
RETURN
    FILTER (
        _distinct,
        IF ( 'Table'[Item] = 3, BLANK (), 'Table'[Date Visit] = [MaxDate] )
    )

     

    1. Create a Measure for calculating the number of rows corresponding to each Item, and you'll end up with the result you want. 

     

    DistinctCount = 
VAR _currentItem =
    MAX ( 'Table 2'[Item] )
RETURN
    COUNTROWS ( FILTER ( ALL ( 'Table 2'[Item] ), 'Table 2'[Item] = _currentItem ) )

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • lnik's avatar
      lnik
      Frequent Visitor
      2 years ago

      Thank you, works perfectly!

      One more question.

      Is it possible if i add slicer for year and month, when selecting a specific month it dynamically to calculate distinct count for the items based on the maximum date for that month?