DimDate filtering messing up Ranking (RANKX) visual table

[peterg0417]

Hi everyone,

This particular issue has been a thorn in my side for a little while now, and I *finally* got to the bottom of what's creating a mess of my table visual, but now the question is WHY?

Below is a screenshot of a ranking table I have created for my business users (names and values were changed for obvious security reasons):

1) It is sorted in ascending fashion

2) I am showing you the bottom of that list because that's where the mess up mainly occurs.

I have a date column in this table, Partner Subset[Date], which is currently filtering the table for March 31st 2023. It looks the way it's supposed to. Even though NAV is 0 for the last 5 entries, they have values for Fully Invested so it's ok. It still looks clean and it looks exactly how I want it to look.

However - let's do the same date filtering but using my DimDate  table:

Now it's ranking the entire list from beginning of time. Please note that the rankings are still correct, it just shows and ranks rows that do not exist in the period selected.

Another example is from the top of the list if we drill in to one of the Partners:

First the correct display filtering on the date column within that table:

And now filtering using DimDate:

First Energy-Unidentified should not be showing! That hasn't existed in the data set since 2021!

Does anyone know how to fix this?

Attached is a copy to the Excel sheet with the data. The Rows section of the Matrix visual contains "Entity" and "Entity-PropertyType". The rest of the measures go into the "Values" section.

Link to Folder with PBIX and Excel

Thanks in advance!

[peterg0417]

Bumping this for more views + added PBIX and Excel file with examples of what I need it to look like.

Thanks,