Forum Discussion
Handling Multiple Date Relationships in Power BI with Client API Filters
We have the following model setup:
A table
column1
column2
expiration date
effective date
DateTable
Date (Relationship: DateTable[Date] is connected to A[effective date])
We’re using the Power BI Client API to apply global filters on DateTable[Date]. This works fine for most visuals that rely on A[effective date].
However, we have one visual that needs to be filtered based on A[expiration date] instead.
We're considering two approaches and would appreciate your feedback:
Option 1: Add calculated columns using USERELATIONSHIP
Enhance A table with new calculated columns:
DAX
column1_exp = CALCULATE([column1], USERELATIONSHIP(DateTable[Date], A[expiration date]))
column2_exp = CALCULATE([column2], USERELATIONSHIP(DateTable[Date], A[expiration date]))
This would allow us to build visuals based on these alternative measures using expiration dates.
Option 2: Duplicate A table for visuals requiring expiration date Create a new B table (essentially a copy of A) and relate it to DateTable[Date] via expiration date. This lets us isolate visuals that rely on expiration date filtering.
A: related to DateTable[Date] via effective date
B: related to DateTable[Date] via expiration date
Hi edwardlee4948,
Thank you for bringing your query to the Microsoft Fabric Community Forum. I also want to thank Deku for their excellent response, which provides a solid foundation to address your scenario.You’ve got a table A with effective date and expiration date, connected to DateTable[Date] via effective date, and you’re using Client API filters on DateTable[Date]. Most visuals work fine with effective date, but you need one visual to filter by expiration date. Let’s look at your options and the best path forward:
- Deku is right to steer you away from this. As they noted with the SQLBI article, calculated columns are static they’re set during refresh and won’t dynamically reflect your Client API filters on DateTable[Date]. This wouldn’t meet your needs for that expiration date visual.
- Creating a second table B linked by expiration date could work, but as Deku pointed out, it duplicates data unnecessarily. This adds extra weight to your model and maintenance overhead, which isn’t ideal unless there’s a specific reason for it.
Deku suggestion using measures with USERELATIONSHIP is the best approach. This lets you stick with one A table and dynamically switch to the expiration date relationship for that specific visual, keeping your model efficient and flexible.
If you find this information useful, please “Accept it as a solution” and give it a “Kudos” to assist others in locating it easily.
Thank you.- Deku is right to steer you away from this. As they noted with the SQLBI article, calculated columns are static they’re set during refresh and won’t dynamically reflect your Client API filters on DateTable[Date]. This wouldn’t meet your needs for that expiration date visual.
3 Replies
Option1 is not recommended .
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Option 2 is also not great as you are duplicating data for not reason
You should create measures that use USERELATIONSHIP instead
Hi edwardlee4948,
Thank you for bringing your query to the Microsoft Fabric Community Forum. I also want to thank Deku for their excellent response, which provides a solid foundation to address your scenario.You’ve got a table A with effective date and expiration date, connected to DateTable[Date] via effective date, and you’re using Client API filters on DateTable[Date]. Most visuals work fine with effective date, but you need one visual to filter by expiration date. Let’s look at your options and the best path forward:
- Deku is right to steer you away from this. As they noted with the SQLBI article, calculated columns are static they’re set during refresh and won’t dynamically reflect your Client API filters on DateTable[Date]. This wouldn’t meet your needs for that expiration date visual.
- Creating a second table B linked by expiration date could work, but as Deku pointed out, it duplicates data unnecessarily. This adds extra weight to your model and maintenance overhead, which isn’t ideal unless there’s a specific reason for it.
Deku suggestion using measures with USERELATIONSHIP is the best approach. This lets you stick with one A table and dynamically switch to the expiration date relationship for that specific visual, keeping your model efficient and flexible.
If you find this information useful, please “Accept it as a solution” and give it a “Kudos” to assist others in locating it easily.
Thank you.- Deku is right to steer you away from this. As they noted with the SQLBI article, calculated columns are static they’re set during refresh and won’t dynamically reflect your Client API filters on DateTable[Date]. This wouldn’t meet your needs for that expiration date visual.
Deku v-ssriganesh Thank you for the answers!
