Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I have a table similar to this:
TABLE1
ID CENTER_ID DATE
1 1 12/03/2022
2 1 13/03/2022
3 2 12/03/2022
4 3 12/03/2022
5 3 13/03/2022
I needed to check which are the "active" records of each center, these would be the ones with the latest date, so I created a calculated column like this:
DAX:
TABLE1
ID CENTER_ID DATE isActive
1 1 12/03/2022 FALSE
2 1 14/03/2022 TRUE
3 2 11/03/2022 TRUE
4 3 12/03/2022 FALSE
5 3 13/03/2022 TRUE
The problem I'm having is that I need it to be filtered on my report by a date filter. So if for example I set the filter to the records that have been created before 12/03/2022, I get:
TABLE1
ID CENTER_ID DATE isActive
1 1 12/03/2022 FALSE
3 2 11/03/2022 TRUE
4 3 12/03/2022 FALSE
But I would like to get:
TABLE1
ID CENTER_ID DATE isActive
1 1 12/03/2022 TRUE
3 2 11/03/2022 TRUE
4 3 12/03/2022 TRUE
Because those records where the active ones on that date.
How could I do this? With a measure, I guess?
Thanks, regards!
Solved! Go to Solution.
@Fátima , Try this measure for latest record
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
@Fátima , Try this measure for latest record
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
Thank you for the tutorials @amitchandak ! I got what I needed!
If you don't mind, I have another question related to this.
I created both measures, so now I can show how many active forms are on a specific date.
Is it possible to filter a chart by the first measure? The one that shows the last record of each CENTER_ID in my case? I'll tell you an example. I have this bar chart that shows results taken from other table, but only based on the active records. Before, when I had the calculated column (isActive), I had a filter in the filter section with isActive=TRUE checked.
How can I filter them now, with this new measure?
Regards!
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |