RANKX By Group In Measure for Push Dataset

[kbsmx]

I have a push dataset (single table with history enabled; the default RealTimeData table name) with a schema similar to the example below.  Notice that there are four rows per site; one for each service within that site.  Each set of rows has a Status Date indicating the date/time of the status information.

Site	Status	Service	Service Status	Status Date
A	Up	AA	Up	2/15/2021 10:02PM
A	Up	BB	Up	2/15/2021 10:02PM
A	Up	CC	Warning	2/15/2021 10:02PM
A	Up	DD	Up	2/15/2021 10:02PM
B	Up	AA	Warning	2/15/2021 10:02PM
B	Up	BB	Up	2/15/2021 10:02PM
B	Warning	CC	Critical	2/15/2021 10:02PM
B	Up	DD	Up	2/15/2021 10:02PM
A	Up	AA	Up	2/17/2021 10:02PM
A	Up	BB	Up	2/17/2021 10:02PM
A	Up	CC	Up	2/17/2021 10:02PM
A	Up	DD	Up	2/17/2021 10:02PM
B	Up	AA	Up	2/17/2021 10:02PM
B	Up	BB	Up	2/17/2021 10:02PM
B	Up	CC	Up	2/17/2021 10:02PM
B	Up	DD	Up	2/17/2021 10:02PM

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.

I've been struggling with this in DAX for quite a while and would grealy appreciate any assitance anyone is able to provide.  Please keep in mind that this is a push dataset, so no calculated columns (only measures).

Thanks!

Kevan

[parry2k]

@kbsmx we are going in a circle, the solution (measure) I provided check the max date of each site, if there are late arrival data, it will take the most recent date of each site, it not max date across all the site but the individual site, not sure what I'm missing here. I think that's pretty much what I understood and maybe I'm not getting your point here, you have to throw example data and show what is not working otherwise we will keep on going back and forth.

[kbsmx]

@parry2k - I hear you, but we're not going in a circle.  It's not behaving the way you describe.  Do you have a push dataset you can test this against?  Perhaps you are testing this with a local data model vs a push dataset which has very different behavior and limitations with some calculations.

Here's what I'm using based on what was provided by @MFelix :

Latest Site Refresh Date = MAXX(SUMMARIZE(ALLSELECTED(RealTimeData), RealTimeData[Site], RealTimeData[Status Date]), RealTimeData[Status Date])

Is Latest Site Data = IF(MAX(RealTimeData[Status Date]) = [Latest Site Refresh Date], 1, 0)

Map visual filter set to:

[kbsmx]

@parry2k @MFelix - Any other ideas?  This is affecting two different reports using two different push datasets.  Because both datasets receive new data every 5 minutes, and it takes approximately 1 minute for the new rows to post, there's about a 20% chance every 5 minutes that a user will see the partial rendering which we need to avoid.  I appreciate your help and understand that this is a difficult nut to crack.

[v-shex-msft]

HI @kbsmx,

I think these should more relate to the Dax formula calculation on 'live' mode data source.  These type of data table that update frequently, they may affect the dax formula calculations.
In my opinion, I'd like to suggest you direct calculate and append these results into the source data and push with these requests at the same time.  It should reduce the effect of Dax calculation that hosts on frequent updates data sources.

Regards,
Xiaoxin Sheng

[kbsmx]

@v-shex-msft Unfortunately, that's not possible.  You can't precalculate the results for this type of filter as you can't query the data in Power BI to produce such a calculation nor can you update existing rows in a push dataset.  This filter *must* be done in DAX based on the current push dataset API limitations.

[parry2k]

@kbsmx I don't think there is anything you can do about it, if data is not in the dataset, what you want the expected behavior to suppose to be?

[kbsmx]

@parry2k - The data *is* in the dataset.  Basically what I want to do is show the latest set of rows for a given site.  That's why I'm trying to find a way to have the visuals show the latest data *per site*.  This way if some site has been updated to a later date, the *latest* date of another site will still be shown.  This would ensure that the user didn't see the missing sites simply because only the latest date was being used across the entire dataset.

This also protects against a failure of the push dataset refresh process. If the process that pushes the data to the push dataset fails to run properly, the most recent successful data pushed will still be shown.  If you just go with a sliding date filter across the entire dataset (which is the way this is behaving), you'll show either an incomplete set of data (as shown above) or no data at all (in the case of a data push failure).

[parry2k]

@kbsmx seems like I'm missing something here but let's start the solution. I created a measure called FILTERMAXDATE and used @MFelix file, so Site A has a max date of 19th, B - 17th, and C - 15th and used a visual level filter on this measure where value = 1 (see attached).

If this doesn't work in your Push Dataset scenario, provide more context with screenshots of what is not working,  and what are the expectations with sample data.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[kbsmx]

@parry2k @MFelix - Here's what's happening:

Refresh while dataset update in progress:

Refresh after dataset update complete:

The push dataset is updated every 5 minutes.  It takes approximately 1 minute for all new rows to be committed to the dataset, and each site's rows are pushed to Power BI in a single batch per site.  So if the user views the report at some point during that 1 minute of pushing data, some sites may still have the previous refresh/status date and therefore aren't shown in the visual as filtered using the provided sliding date window.

[MFelix]

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.

---

[kbsmx]

@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!

[MFelix]

Hi @kbsmx ,

I cannot replicate this on any of my models, but I will callout some users that probably can help you out.

@parry2k , @Greg_Deckler 

[kbsmx]

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).

[MFelix]

Hi @kbsmx ,

Based on your reply I took my answer as correct. Let see if the users I call out can help you.