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.
Community SupportHi postvijay
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.
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.
Hi Srikanth,
Sorry for late reply but i couldnt login to the portal.
The challenge I am facing is in modelling: snowflake schema created out of few star schema
Table structure:
- I have tables in star schema having 7 tables (A1 to A7) attached to central table A.
- One more star schema having 3 tables (B1 to B3) attached to central table B
- and one more star schema which connects 4 tables (C1 to C4) to its central table C
- and some more similar strucutred tables.
Issues:
- As powerbi by default connects using inner join, and in case when some relation doesnt exists in table B or C, my entire row in Table A also vanishes though table A has valid data.
- I am able to achieve the results
- by calculating each attribute against date and by passingthrough hoops to get data from table c4 against attribute in table A1
- this appraoch makes powerbi too slow.
- the dax cant be used as slicer hence its cant be used infull capacity
- as all tables have from_date and To_date --> the key columns repeat and hence we have to join all these tables in many-many --> which i feel is bad design.
- there are around 500K records already and I am trying to get data for last 10+ years hence expanding each table on each business date kills powerbi .
need any best approach to handle bitemporal tables and without loosing data and with best performance.