Handling Multiple Date Relationships in Power BI with Client API Filters

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.