Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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 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.
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 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.
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
User | Count |
---|---|
5 | |
4 | |
4 | |
2 | |
2 |
User | Count |
---|---|
8 | |
4 | |
4 | |
4 | |
3 |