Forum Discussion
Counting Unique Values Level based on last timestamp
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.
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
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 __resultHi 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....