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
Sorry for the late response.
To handle filtering blanks on bitemporal tables in Power BI, especially when using date ranges across multiple tables with ValidFrom and ValidTo columns, you can follow this approach:
- Create a separate date filter table using the CALENDAR function, for example:
DateFilter = CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)) - In each of your bitemporal tables (e.g., TableA, TableB), create a calculated column to check if a row is in the selected date range:
IsInRange =
VAR SelectedStartDate = MIN('DateFilter'[Date])
VAR SelectedEndDate = MAX('DateFilter'[Date])
RETURN
IF(
[ValidFrom] <= SelectedEndDate &&
(ISBLANK([ValidTo]) || [ValidTo] >= SelectedStartDate),
1,
0
) - This logic includes rows where ValidTo is blank (interpreted as "still valid").
In your visuals or filters, include only the rows where IsInRange = 1.
This method allows you to control date-range filtering even when some of your validity periods are open-ended (i.e., have blank ValidTo values), without breaking the logic in visuals or relationships.
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.