Forum Discussion
RANKX By Group In Measure for Push Dataset
Hi kbsmx ,
If you only want to pickup the maximum value for each site why don't you pick up the maximum value based on selection and then using a different measure you filter your data something similar to:
MaxDate =
MAXX (
SUMMARIZE (
ALLSELECTED ( 'Filtering_On_Max_Date' ),
'Filtering_On_Max_Date'[Site],
Filtering_On_Max_Date[Status Date]
),
'Filtering_On_Max_Date'[Status Date]
)
filter = IF(MAX('Filtering_On_Max_Date'[Status Date] ) = [MaxDate] , 1)
See attach file.
If this is not the expected result can you please explain a little bit better waht you mean by:
kbsmx wrote:
What I need to do is rank the dates by latest to oldest; latest date site rows having a rank of 1, next oldest 2, and so on. I need the ranking to be done per site; indicating the latest status date per site. The idea is that I can use this to filter visuals to include only the latest status data for a given site.
MFelix - I think you've keyed in on what I'm looking for; the ultimate goal is to only show the latest set of rows for each site based on that MAX of that site's status date.
However, there are two issues with your proposed solution given the context of a push dataset:
1) You can't reference push dataset RealTimeData table columns directly outside of a row context; so only interative functions and filters have access to them. Only measures can be referenced at the "top" level, which is indeed very frustrating. There are many ways I can get a row context within a nested calculation, but the issue becomes surfacing the result to the top level. E.g., calculate the max status date for a given site, compare each row's status date for that site against the site max date, then return a 1 or 0 based on whether there is a match.
2) The problem with the "based on selection" approach is that in when a map visual is used and it is first loaded, there is no selection context. So nothing is "selected" and the map only cares about the fields you've used for latitude, longitude, color, size, etc.. This makes it very difficult to filter the map visual to only show sites based on their most recent data. Using something like a page-level filter basd on status date, say within the last 5 minutes doesn't work either because if the visual loads in the middle of a data refresh, some site;s status rows may not have been committed to the push dataset on the Power BI quite yet, which then causes the map to only show a subset of the sites. This is why I need some sort of "sliding window with site context" solution.
I hope the above makes sense. Thanks!
Hi kbsmx ,
I cannot replicate this on any of my models, but I will callout some users that probably can help you out.
It appears that your solution is working, so I marked it as accepted as the solution. Thanks!MFelix - Actually, I just ran into the same issue using your solution as the behavior I mentioned in the map partial sites rendering problem I mentioned in my first reply. The map only rendered a subset of the sites as the report refresh occurred during the dataset update and some of the sites hadn't yet updated (they had the date/time 5 minutes before the latest refresh).