Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to calculate the score of someone based on its results compared to the results of the other users. I'm currently stuck when I filter the table. Let me explain.
I have users and categories. Each user has a result per category and the date when he got his result.
I would like to get, for each row, the score which is the result compared to the previous ones.
The range of values to take for 1 row (so for 1 manager in 1 category at a certain date) is the last result of each user for this category before the date of the row.
Getting all the results for a single category before the date is fairly easy but I can't find a way to only take the last results of each user in this range.
VAR PreviousScores= FILTER( 'Results' ,
'Results'[CatID] = EARLIER( 'Results'[CatID] )
&& 'Results'[Date] <= EARLIER( 'Results'[Date] )
)
This gives me the results for the category and before the date of the current row.
But within those rows, I only need the last results for each user. This is where I'm stuck.
Row | Username | Date | Result |
2 | User 1 | 10/01/2020 | 30 |
3 | User 1 | 15/10/2019 | 20 |
4 | User 1 | 10/09/2019 | 24 |
5 | User 2 | 10/01/2020 | 50 |
6 | User 2 | 15/10/2019 | 45 |
7 | User 2 | 10/09/2019 | 33 |
8 | User 3 | 10/01/2020 | 10 |
9 | User 3 | 15/10/2019 | 22 |
10 | User 3 | 10/09/2019 | 35 |
11 | User 4 | 4/01/2020 | 56 |
12 | User 4 | 5/10/2019 | 34 |
13 | User 4 | 2/09/2019 | 23 |
For example, in this scenario, I have the results for 1 category. To get the scores, here are the values I should use:
What would be the best way to filter these rows in my DAX function for the calculated column?
Thank you very much for your help
The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.
My Recent Blog -
Time Intelligence - Direct Query
Hi,
Sorry for the delay, here a sample of the data. In the table, I added a calculated column. This column only returns the number of rows to consider. This number should, in this case, always be 4 or 3 for the very first ones.
In any case, max 4 as there are only 4 users and I always need the last score for each of them.
Thank you very much for your help,
Hi,
I'll try to give more explanations. I'll provide a pbix file as soon as I can.
So I have a table with all the results for every user in every category. One user can have multiple results for the same category. He got them at different dates.
So my table has the following column: UserID, CategoryID, Date and Result.
For every row, I need to compute a score. This score is based on all the latest results of each user previous to this one.
So I'm adding a calculated column to do this. In it, I'm filtering this table to only get the results that I need and this is where I'm stuck, I cannot find a way to filter the table to get what I need.
So my first step was to get all the results previous to the row.
VAR PreviousResults = FILTER( 'Results' ,
'Results'[CategoryID] = EARLIER( 'Results'[CategoryID] )
&& 'Results'[Date] <= EARLIER( 'Results'[Date] )
)
But it's not finished quite yet because if User 1 has 3 results for this category before the row, I want to only get the latest because all the older ones are irelevant. This is what I say when I write "All the latest results of each user before the row".
I'm stuck at this second step.
Considering the following table is the results for 1 category:
Username | Date | Result |
User 1 | 1/01/2020 | 30 |
User 1 | 1/12/2019 | 40 |
User 1 | 1/11/2019 | 33 |
User 1 | 1/10/2019 | 45 |
User 1 | 1/09/2019 | 22 |
User 2 | 1/01/2020 | 35 |
User 2 | 1/12/2019 | 23 |
User 2 | 1/11/2019 | 65 |
User 2 | 1/10/2019 | 10 |
User 2 | 1/09/2019 | 30 |
User 3 | 2/01/2020 | 45 |
User 3 | 2/12/2019 | 60 |
User 3 | 2/11/2019 | 50 |
User 3 | 2/10/2019 | 24 |
User 3 | 2/09/2019 | 60 |
In my new column, for the first row which is User 1 on the 01/01/2020, I need the latest scores of all the user on the 01/01/2020. So I'll get User 1 on the 01/01/2020, User 2 on the 01/01/2020 and User 3 on the 02/12/2019 (This is its 1st score past to 01/01/2020).
For User 1 on the 01/12/2019, I need User 1 on the 01/12/2019, User 2 on the 01/12/2019 and User 3 on the 02/11/2019.
For User 3 on the 02/01/2020, I need User 1 on the 01/01/2020, User 2 on the 01/01/2020 and User 3 on the 02/01/2020.
For User 3 on the 02/12/2019, I need User 1 on the 01/12/2019, User 2 on the 01/12/2019 and User 3 on the 02/12/2019.
I hope this makes more sense. Let me know if I need to provide more explanations.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |