Forum Discussion
Counting Unique Values Level based on last timestamp
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 :
| Country | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 | Column Total |
| United Kingdom | 1000 | 500 | 200 | 500 | 1234 | 3434 |
| United States | 500 | 300 | 100 | 1000 | 3254 | 5154 |
| Japan | 300 | 200 | 300 | 100 | 6549 | 7449 |
| Germany | 200 | 100 | 100 | 5123 | 9477 | 15000 |
| Rows Total | 2000 | 1100 | 700 | 6723 | 20514 | 31037 |
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
| Country | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 | Total |
| XXXX | 1 | 2 | 1 | 0 | 1 | 5 |
| Total | 1 | 2 | 1 | 0 | 1 | 5 |
Where "Level 1" = 1 due to
| CustomerID | Timestamp | Level |
| 2222 | 01/02/2023 | Level 1 |
| 2222 | 01/03/2023 | Level 1 |
| 2222 | 01/04/2023 | Level 1 |
Level 2 = 2
| CustomerID | Timestamp | Level |
| 1111 | 01/01/2023 | Level 1 |
| 1111 | 01/03/2023 | Level 1 |
| 1111 | 10/18/2023 | Level 2 |
| 5555 | 05/05/2023 | Level 1 |
| 5555 | 05/06/2023 | Level 3 |
| 5555 | 05/07/2023 | Level 2 |
Level 3 = 1
| CustomerID | Timestamp | Level |
| 3333 | 05/06/2023 | Level 1 |
| 3333 | 05/07/2023 | Level 2 |
| 3333 | 05/08/2023 | Level 3 |
Level 4 = 0
As there is no-one level 4 last date.
Level 5 = 1
| CustomerID | Timestamp | Level |
| 4444 | 03/03/2023 | Level 4 |
| 4444 | 03/04/2023 | Level 4 |
| 4444 | 03/05/2023 | Level 5 |
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
MaxDatePerLevel for Level 2 is as below
Hi some_bih
In my actual data is a timestamp dd/mm/yyyy hh:mm:ss.
In the sample I meant to have dd/mm/yyyy, but from what I see now the I have 1 not valid date for CustomerID 1111 as 10/18/2023 instead of 18/10/2023. The rest are ok.
For Level 4 is 0 because on the last entry(timestamp) of customerID 4444 he is on Level 5.
I believe my main issue here is that not the actual count, but how to make make a summarize table with only the last date for each customer ID as follows :
CustomerID Timestamp Level 1111 18/10/2023 Level 2 2222 01/04/2023 Level 1 3333 05/08/2023 Level 3 4444 03/05/2023 Level 5 5555 05/07/2023 Level 2 If I have this table (as a temporary table in a measure), the count part is simple.
Also thank you, I think you gave me an idea now...
If I put the maxdate in the summary table then I might be able to work out the max/latest level per customer....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