Forum Discussion
Get latest status based on same identifier
Hi everyone, I want to develop this, basically, it will display status based on the latest status accoridng to the Linked column..if the user did not select any specific quarter/year...but if they select Q1 2025, the status will be displayed as Ongoing. How can i do this?
HI IkmalFikri900
The file shared by Ashish_Excel provides accurate results as per the request.
To display 200 in your card, I would recommend using Latest measure where it will correctly display the expected value.
See below image for reference-
Reuploadiing the pbix for reference.
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
16 Replies
try this:
CurrentStatus :=
VAR IsFiltered =
ISFILTERED('Calendar'[Quarter]) || ISFILTERED('Calendar'[Year])VAR SelectedLinkedDate =
IF(
IsFiltered,
MAX(ActivityTable[Linked]), -- Use the latest date in selected period
CALCULATE(
MAX(ActivityTable[Linked]),
ALL(ActivityTable) -- Ignore slicers if nothing is selected
)
)RETURN
CALCULATE(
MAX(ActivityTable[Status]),
FILTER(
ActivityTable,
ActivityTable[Linked] = SelectedLinkedDate
)
)Using this formula, when no specific quarter/year selected, it display Ongoing instead of Completed.
Can you assist?
you can try to create a new table
Table 2 = DISTINCT('Table'[Quarter])then create a measureMeasure = if(max('Table'[Quarter])=max('Table 2'[Quarter]),1)add this measure to visual filter and set to 1pls see the attachment belowHi, thanks for the suggestions, but it doesn't fit my requirement, can you assist?
then pls provide more details about your request. what is the expected output?
Update
Sample Data:ID LinkedID Date StatusUpdate Values IssueStatus 1 1 1-Jan-25 Update 1 100 Ongoing 2 1 2-Feb-25 Update 2 150 Completed 3 3 1-Jan-25 Update 3 200 Ongoing 4 3 2-Feb-25 Update 4 200 Ongoing So, the expectation is, when User filter ALL Date Column, visuals should display this
Info Ongoing Cases 1 Completed Cases 1 Values 350 Since for the latest item (ie February), the latest IssueStatus is Completed and Latest Value is 150 for LinkedID 1. but LinkedID 2 stays the same
When the user Filter January 2025 only, visuals should display:
Info Ongoing Cases 2 Completed Cases 0 Values 300 Since in January, both LinkedID 1 and 2 status is Ongoing and the value is 100+200
Thanks. it helps a lot,
Just the Values/T amount where if no filter is selected for ongoing, it shows 500 where it should shows 200 only
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!
Best Regards,
Community Support Team _ C Srikanth.Hi, apologies for the reply,
I still havent' been able to resolved the issues.ID LinkedID Date StatusUpdate Values IssueStatus 1 1 1-Jan-25 Update 1 100 Ongoing 2 1 2-Feb-25 Update 2 150 Completed 3 3 1-Jan-25 Update 3 200 Ongoing 4 3 2-Feb-25 Update 4 200 Ongoing So, the expectation is, when User filter ALL Date Column, visuals should display 1 Ongoing and 1 Completed
While if the user select January, it should be 2 Ongoing case 0 Completed
The issue is i can use Measure but i want to use card to visualize the number of case and values where i can filter using just Status.
So i have to use calculated column where the expected results is:When no filter is selected:
ID LinkedID Date StatusUpdate Values IssueStatus Latest Status 1 1 1-Jan-25 Update 1 100 Ongoing Completed 2 1 2-Feb-25 Update 2 150 Completed Completed 3 3 1-Jan-25 Update 3 200 Ongoing Ongoing 4 3 2-Feb-25 Update 4 200 Ongoing Ongoing But when January is Selected (The table is filtered)
ID LinkedID Date StatusUpdate Values IssueStatus Latest Status 1 1 1-Jan-25 Update 1 100 Ongoing Ongoing 3 3 1-Jan-25 Update 3 200 Ongoing Ongoing Is this possible?
HI IkmalFikri900
The file shared by Ashish_Excel provides accurate results as per the request.
To display 200 in your card, I would recommend using Latest measure where it will correctly display the expected value.
See below image for reference-
Reuploadiing the pbix for reference.
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!Best Regards,
Community Support Team _ C Srikanth.
