Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello, I am rather new and looking to figure out this via measure(s). I can't seem to find a suitable answer in past answers that's working for me. Also, please let me know if this is something better tackled via query.
I am working with the first table, and looking to create the third matrix. The middle filtered table is just for context. I need to first isolate the most recent dates and grades, then I need to understand how to calculate what only the most recent gradings are as a percentage of A, B, and C.
Start:
Grade | Restaurant | Date |
a | Bob | 2/15/2022 |
a | Bob | 1/15/2022 |
c | Bob | 3/1/2022 |
b | Sue | 12/13/2021 |
a | Sue | 2/1/2022 |
b | Sue | 3/3/2022 |
c | Greg | 2/1/2022 |
a | Greg | 1/31/2022 |
c | Greg | 1/15/2022 |
b | Miss | 1/8/2022 |
b | Miss | 1/9/2022 |
a | Miss | 1/20/2022 |
c | Jon | 2/3/2022 |
a | Jon | 1/3/2022 |
b | Jon | 3/3/2022 |
b | Pam | 12/2/2021 |
a | Pam | 9/8/2021 |
c | Pam | 6/2/2021 |
Isolate latest:
Grade | Restaurant | Date |
c | Bob | 3/1/2022 |
b | Sue | 3/3/2022 |
c | Greg | 1/31/2022 |
a | Miss | 1/20/2022 |
b | Jon | 3/3/2022 |
b | Pam | 12/2/2021 |
Final Matrix:
Grade | % Restaurants (Latest) |
a | 17% |
b | 50% |
c | 33% |
Thank you in advance to all that help!
Solved! Go to Solution.
@Anonymous , Please find the file attached with solution after signature
Hi @Anonymous
ALLSELECTED : Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.
Regarding MAX, my understanding is that returning the [Restaurant] of the current table is compared to the [Restaurant] in the virtual table, and when the value of 2 [Restaurant] are the same, the largest date is returned .
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Please find the file attached with solution after signature
Hi, Thank you so much for the response!
I just have one point of confusion so far, about the measure(s) which are getting the Last Date/Grade. Below is how I'm reading it an my question:
Last Date is doing the following:
Creating a variable (_max) for the most recent date
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
24 | |
21 | |
21 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |