Forum Discussion
Filtering which includes Blanks on Bitemporal Tables
Hi postvijay
Sorry fo the late response.
PLease do check the below detailed steps that might resolve your issue.To address performance and data consistency issues in your Power BI model with bitemporal and snowflake schema design, consider flattening related tables (like A1–A7, B1–B3, etc.) into their respective central tables (A, B, C) using Power Query or SQL views. This reduces the risk of data loss from inner joins and improves performance.
Introduce a centralized Date table and map your entities using a valid-from/to structure in a separate mapping table. Use DAX functions like TREATAS() or USERELATIONSHIP() to apply custom date filters without expanding millions of rows at runtime.
Avoid direct many-to-many joins by using bridge tables or by pre-processing the mappings into your model to reduce relationship complexity. When handling large historical data, split the model using composite mode: recent data can be imported for fast access, while older records can be accessed via DirectQuery or summarized tables.
Wherever possible, push filtering and bitemporal logic into the data source to avoid expensive transformations in Power BI. Avoid using DAX-generated values in slicers; instead, build static slicer tables with predefined time ranges or categories.
Finally, leverage incremental refresh for long-term datasets to prevent full refreshes and maintain efficient query performance.
This combined modeling approach should provide a more maintainable, scalable, and performant solution. Let me know if you'd like help structuring this with sample schema or visuals.
If all your tables are in import mode, you can use Power query to prepare your data.
You can follow these steps-- Import all your bi-temporal tables.
- Create all possible ID-Date combinations (through calendar table cross join).
- Merge tables one-by-one using Left Joins based on:
ID match
Date between From and To
This results in a flattened structure where unmatched data from newly introduced tables will result in nulls, not blanks.
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.
Super UserHi postvijay ,
I think you're working with a bitemporal model in Power BI and aiming to simplify user interactions without losing date-valid accuracy — a common challenge when handling time-bound records across multiple tables.
For Issue 1:
- Create a 'Date Filter' table like this:
DateFilter =
CALENDAR ( DATE(2020, 1, 1), DATE(2030, 12, 31) )
- Then, in each of your bitemporal tables (e.g., TableA, TableB, etc.), create a calculated column like:
- Now, instead of filtering visuals one-by-one, create a composite table or model where you use a FILTER over only IsInRange = 1.
- To avoid writing filters on each visual, you can create calculated tables that only include valid rows:
ValidTableA =
FILTER (
TableA,
TableA[IsInRange] = 1
)- Then use ValidTableA in your visuals instead of raw TableA.
This way, the filter context is centralized via the slicer, and visuals stay clean.
For Issue 2:
Use Left Joins + Default/Placeholder Records
Power BI doesn’t have native SQL-like joins for relationships, but we can simulate this in DAX using LOOKUPVALUE, TREATAS, or LEFT JOIN-style merging in Power Query.
We can do this with 2 Approaches:
Option A: Use LOOKUPVALUE with a Fallback
In your main table, when referencing other tables:
Attribute_n_From_B =
LOOKUPVALUE (
TableB[Attribute_n],
TableB[ID], TableA[ID],
TableB[IsInRange], 1
)
And use IF(ISBLANK(...), "No Match", ...) to prevent full row blanks.
Option B: Power Query Merge with Placeholder Row
In Power Query:
- Create a row in each table with ID = -1, FromDate = 1900-01-01, ToDate = 9999-12-31, and all columns set to "Unknown" or null.
- When merging tables, always do a Left Outer Join and fallback to this row if there’s no match:
- This prevents matrix visuals from going fully blank when one side lacks data for a given date.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]
Thanks for reply, I will try this approach today. Just wondering, does.calculated table takes too much memory and is it dynamically.changes based on user date range selection?