Circular Dependency

[Kashuo]

Seems like these circular dependency issues can be unique snowflakes...but maybe you can help.

This problem is similar to a previous post of mine, so I'm going to reuse some wording here. But it's a new problem.

@MattAllington and @v-jiascu-msft will be familiar.

My data is from the real estate development industry.

• I have 3 tables: Property, Floor, and Zone.
• Properties have multiple Floors, and Floors have multiple Zones. (In other words: Each Zone row has a Floor ID field of the Floor it belongs to, and each Floor row has a Property ID field of the Property it belongs to).
• The three tables are joined on their respective IDs.
• Each Zone has a field called Opening Date, which is the date we finish construction on that Zone (a portion of a Floor).
• It's easy to filter a table visualization of Zones by Zone Opening Date.
• In order to filter a table visualization of Floors by Earliest Floor Opening Date, I created a new column with this formula: Earliest opening date per floor = CALCULATE(MIN('zones'[zone_opening_date]))
• Seems like this should work, however it gives me a circular dependency error.
• If I take out the calculate, then I don't get the error, but I also don't get the earliest date per floor, I get the earliest of all dates. 
• If I use a Measure instead of a column, then I don't get the circular error, and indeed the correct date shows up in the floor list, but then I can't use the range slicer to filter the floors, like I want to do in my image (the arrows imply which views should be affected by which date slicers). 

[MattAllington]

this data looks like a natural hieriarchy to me.  Have you considered denormalising the data into a single table?

[Kashuo]

Do you mean merging the three tables inside powerbi? Or creating that denormalized table on the server and then bringing it into PowerBi as a single table?

Reason I ask is that neither option is great for me at this point. Heres why:

Option 1: I have tried this before and the issue is that PowerBI has a tendency to generate many millions of extra rows so that it takes forever to refresh the data (and, truth be told, those three tables are actually already involving some joins of multiple tables on the inside PowerBI, which may be where the circular dependency is coming from...). This tends to make things pretty messy and confusing and prone to break. Partly my own limitations, admitadly.

Option 2: I work for a large busy organization with limited data engineering resources. It's hard for me to get that kind of table built and supported on our server because it means devoting engineer resources to it which don't report to me. If this is really the best option, than I could perhaps elevate this, but I'm trying to be self sufficient. 

Is this making sense? 

[MattAllington · Accepted solution]

I mean merging before/during load.  How big are your tables?  Have you tried using Power Query to do this?

[Kashuo]

Ok, yes, I managed to get some engineering help to build a new view on Redshift which pre-joined the key data. Thank you. They used SQL to create the view. Haven't tried Power Query.