Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
CustomerID | Timestamp | Level |
1111 | 1/1/2023 | Level 1 |
1111 | 1/3/2023 | Level 1 |
1111 | 10/18/2023 | Level 2 |
2222 | 1/2/2023 | Level 1 |
2222 | 1/3/2023 | Level 1 |
2222 | 1/4/2023 | Level 1 |
3333 | 5/6/2023 | Level 1 |
3333 | 5/7/2023 | Level 2 |
3333 | 5/8/2023 | Level 3 |
4444 | 3/3/2023 | Level 4 |
4444 | 3/4/2023 | Level 4 |
4444 | 3/5/2023 | Level 5 |
5555 | 5/5/2023 | Level 1 |
5555 | 5/6/2023 | Level 3 |
5555 | 5/7/2023 | Level 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
Solved! Go to Solution.
Hi @aabi please create another column to check true false last date for customer
Did I answer your question? Kudos appreciated / accept solution!
Output
Proud to be a Super User!
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.
Proud to be a Super User!
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
Proud to be a Super User!
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
Did I answer your question? Kudos appreciated / accept solution!
Output
Proud to be a Super User!
Hi @aabi
If this is solution, please accept it so other member of community could use it. Thank you
Proud to be a Super User!
Hi @some_bih
While what you made kind of works, but is not something that I can use on my model.
However with your assistance/inspiration, I have managed to create the formula that I need as follows :
Level 1 Count = VAR LatestTimestampPerCustomer =
ADDCOLUMNS(
SUMMARIZE(
'Table1',
'Table1'[CustomerID],
"MaxTimestamp", MAX('Table1'[Timestamp])
),
"LatestLevel",
CALCULATE(
MAX('Table1'[Level]),
FILTER(
'Table1',
'Table1'[CustomerID] = EARLIER('Table1'[CustomerID]) &&
'Table1'[Timestamp] = EARLIER([MaxTimestamp])
)
)
)
RETURN
COUNTROWS(
FILTER(
LatestTimestampPerCustomer,
[LatestLevel] = "Level 1"
)
)
I just need to change the "Level 1" to "Level 2" on the countrows for each level. Results will be as follows :
Level 1 | Level 2 | Level 3 | Level 4 | Level 5 |
1 | 2 | 1 | 0 | 1 |
Hey @aabi ,
what is the expected result based on the sample data you provided?
Regards,
Tom
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)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
106 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |