Handling Multiple Date Relationships in Power BI with Client API Filters

[edwardlee4948]

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

[edwardlee4948]

@Deku @v-ssriganesh Thank you for the answers!

[v-ssriganesh · Accepted solution]

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]

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