Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi community,
I am trying achieve probably simple thing. I have table:
Order of state is:
1. in_progress
2. lost
3. done
Main goal is to know how many users are in which state = their last state = aaa and ccc is done; bbb is lost.
If you have some tips for nice visualization of their flow/funnel it will be great. And/or some pie chart with "% of users who started is still in_progress, % reach done,.." 🙂
Tricky part for me is that user can have multiple same state, but I need to calculate only last.
I am trying to improve my measure, but without success for now
users_done = CALCULATE(DISTINCTCOUNT('mytable'[owner_id]),FILTER('mytable','mytable'[state]="done"))
Thanks in advance!
Solved! Go to Solution.
Hi @Divous ,
We could first create a measure (date_max) to calculate the last time of each user, then calculate the number of users in different status.
step 1: Add measure (date_max)
date_max =
CALCULATE (
MAX ( 'max date'[date] ),
FILTER ( ALL ( 'max date' ), 'max date'[user] = MAX ( 'max date'[user] ) )
)
step 2: Add measure(count )
count =
CALCULATE (
COUNTROWS ( 'max date' ),
FILTER ( 'max date', [date] = [date_max] )
)
The final output is shown below:
Best regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Divous ,
We could first create a measure (date_max) to calculate the last time of each user, then calculate the number of users in different status.
step 1: Add measure (date_max)
date_max =
CALCULATE (
MAX ( 'max date'[date] ),
FILTER ( ALL ( 'max date' ), 'max date'[user] = MAX ( 'max date'[user] ) )
)
step 2: Add measure(count )
count =
CALCULATE (
COUNTROWS ( 'max date' ),
FILTER ( 'max date', [date] = [date_max] )
)
The final output is shown below:
Best regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Why should the answer for bbb be lost. Shouldn't it be in_progress? Also, share data in a form that can be pasted in an MS Excel file.
Hi @Ashish_Mathur,
thanks for your response!
My table was just a simplified example, sorry. So I'm attaching original data, from which it will be clearer:
https://ufile.io/fg3zpaog
There may be the same status for the user, as the process may start multiple times - sometimes end in done, other times in_progress, or fail at all (lost), or he finished, but without data (no_data).
Hi,
On a smaller dataset show me the expected result in another tab of the Excel file.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
72 | |
39 | |
29 | |
27 |
User | Count |
---|---|
97 | |
96 | |
58 | |
44 | |
40 |