Forum Discussion

aabi's avatar
aabi
Helper I
3 years ago
Solved

Counting Unique Values Level based on last timestamp

Hello All

 

I have been trying but with no effect to count a the numbers of users that are on a certain level. But only the MAX of a certain time stamp

 

As a sample the table below

 

CustomerIDTimestampLevel
11111/1/2023Level 1
11111/3/2023Level 1
111110/18/2023Level 2
22221/2/2023Level 1
22221/3/2023Level 1
22221/4/2023Level 1
33335/6/2023Level 1
33335/7/2023Level 2
33335/8/2023Level 3
44443/3/2023Level 4
44443/4/2023Level 4
44443/5/2023Level 5
55555/5/2023Level 1
55555/6/2023Level 3
55555/7/2023Level 2

 

a measure for each level to count the latest status of a user without double counting multile levels.

By using the a distinct count, as I do not take into consideration the date

Level 1 Count = CALCULATE(DISTINCTCOUNT(Table[CustomerID]),FILTER(Table,Table[Points]= " Level 1"))
I'm double counting Level 1 for all unique customerID
 
When I try to put in the date, I only get as a result 1 (as it filters all dates and keeps only the max date?)
Example that I use 
 
Level 1 Count = 
VAR MaxDate =
    MAX ( Table[Timestamp] )
VAR _STATUS =
    FILTER (
        Table,
        Table[Level] = Level 1
            && Table[Timestamp] = MaxDate
    )
VAR _Count =
    COUNTROWS (DISTINCT( _STATUS ))
RETURN
    _Count
 
It should be noted that is possible for me to get the above numbers, by adjusting the table that I'm counting and sorting by date and then removing all duplicates (customerID), so I effectively have only 1 entry per customerid(and is the latest). But in this case I cannot use a slicer for the dates
 
Anyones has any idea how to make this measure?
 
 
Need Help
  • some_bih's avatar
    some_bih
    3 years ago

    Hi aabi please create another column to check true false last date for customer

    True False LastDateForCustomer = Sheet1[MaxDatePerCutomer]=Sheet1[Timestamp].
    After that create overvies as shown on Output (slice True)

    Did I answer your question? Kudos appreciated / accept solution!

     

     

    Output

     

9 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    Hey aabi ,

     

    what is the expected result based on the sample data you provided?

     

    Regards,

    Tom

    • aabi's avatar
      aabi
      Helper I
      3 years ago

      Hi TomMartens 

       

      Well my sample might not be the best, but the results should have been 

      Level 1 Count = 1

      Only UserID 2222, is level 1 on the last date.

      I would also have the same formula as "level 2 count" which should be 2 (UserID 1111 and 5555)

       

       

       

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi aabi as TomMartens wrote what is expected output.Below I created cal. column from your picture for MaxDatePerCustomer.  In your data same each customer at some date have only one Level ID, no two Level ID's at same date so countidistinct always return 1

    Hope this help, kudos appreciated.

     

    MaxDatePerCutomer =
    VAR __max=MAX(Sheet1[Timestamp])
    VAR _customer=Sheet1[CustomerID]
    VAR __result=
        CALCULATE(
        MAX(Sheet1[Timestamp]),
        FILTER(ALL(Sheet1),Sheet1[CustomerID]=_customer)
        )
    RETURN __result

     

     

     

     

    • aabi's avatar
      aabi
      Helper I
      3 years ago

      Hi some_bih 

       

      Thank you for your input, however that that does not really help.

       

      Let me explain a bit more what I need.. first of all the dataset I have has around 20K users. From 1 timestamp up to 10+ timestamps, and someone can start from level 1 and become level 5.

       

      The endresults will look as follows :

       

      CountryLevel 1Level 2Level 3Level 4Level 5Column Total
      United Kingdom100050020050012343434
      United States500300100100032545154
      Japan30020030010065497449
      Germany2001001005123947715000
      Rows Total2000110070067232051431037

       

      The country exist in another table (unique based on customerID, so that not an issue)

       

      Level 1,2,3,4,5 and Total - Columns would be DAX measures
      Column Total = Level 1 + 2+3+4+5
      Rows Total = Level Dax Formula count

      Espected results based on dataset that I have posted

      CountryLevel 1Level 2Level 3Level 4Level 5Total
      XXXX121015
      Total121015


      Where "Level 1" = 1 due to 

       

      CustomerIDTimestampLevel
      222201/02/2023Level 1
      222201/03/2023Level 1
      222201/04/2023Level 1

       

      Level 2 = 2

       

      CustomerIDTimestampLevel
      111101/01/2023Level 1
      111101/03/2023Level 1
      111110/18/2023Level 2
      555505/05/2023Level 1
      555505/06/2023Level 3
      555505/07/2023Level 2

       

      Level 3 = 1

       

      CustomerIDTimestampLevel
      333305/06/2023Level 1
      333305/07/2023Level 2
      333305/08/2023Level 3

       

      Level 4 = 0


      As there is no-one level 4 last date.

      Level 5 = 1

       

      CustomerIDTimestampLevel
      444403/03/2023Level 4
      444403/04/2023Level 4
      444403/05/2023Level 5

       

      • some_bih's avatar
        some_bih
        Community Champion
        3 years ago

        Hi aabi I do not fully understand your logic, let me explain why not.

        In your data, format of date is DD/MM/YYYY?

        For Level 1 I have that Max date per level is as shown on picture below for Cal. column MaxDatePerLevel

         
        Question for Level 4: how that for Level 4 you expect 0 when are some customer and dates?

        MaxDatePerLevel for Level 2 is as below

         

        MaxDatePerLevel =
        VAR __max=MAX(Sheet1[Timestamp])
        VAR __level=Sheet1[Level]
        VAR __result=
            CALCULATE(
            MAX(Sheet1[Timestamp]),
            FILTER(ALL(Sheet1),Sheet1[Level]=__level)
            )
        RETURN __result