Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Solved! Go to Solution.
Hi @LearnToFly , Hope you are doing good!
To manage multiple date fields in Power BI, create a calendar table with an active relationship to Date Completed and inactive relationships to Date Filled and Date Written. Use the USERELATIONSHIP DAX function to activate these relationships in measures. For example:
Total Completed = SUM(FactTable[Value])
Total Filled = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateFilled], Calendar[Date]))
Total Written = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateWritten], Calendar[Date]))
Example of Using Slicers to Switch Dates
You can also add a slicer or parameter to allow users to switch between these date fields dynamically. This can be done using a disconnected table and SWITCH function.
Create a Date Selection Table:
Create a table with options for each date field. For example:
DateSelection
Date Completed
Date Filled
Date Written
Create a Measure to Select Date:
SelectedDateMeasure =
SWITCH (
SELECTEDVALUE(DateSelection[DateSelection]),
"Date Completed", [Total Completed],
"Date Filled", [Total Filled],
"Date Written", [Total Written],
BLANK()
Add Slicer and Use Measure:
Add the DateSelection column as a slicer to your report and use the SelectedDateMeasure in your visuals.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi @LearnToFly , Hope you are doing good!
To manage multiple date fields in Power BI, create a calendar table with an active relationship to Date Completed and inactive relationships to Date Filled and Date Written. Use the USERELATIONSHIP DAX function to activate these relationships in measures. For example:
Total Completed = SUM(FactTable[Value])
Total Filled = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateFilled], Calendar[Date]))
Total Written = CALCULATE(SUM(FactTable[Value]), USERELATIONSHIP(FactTable[DateWritten], Calendar[Date]))
Example of Using Slicers to Switch Dates
You can also add a slicer or parameter to allow users to switch between these date fields dynamically. This can be done using a disconnected table and SWITCH function.
Create a Date Selection Table:
Create a table with options for each date field. For example:
DateSelection
Date Completed
Date Filled
Date Written
Create a Measure to Select Date:
SelectedDateMeasure =
SWITCH (
SELECTEDVALUE(DateSelection[DateSelection]),
"Date Completed", [Total Completed],
"Date Filled", [Total Filled],
"Date Written", [Total Written],
BLANK()
Add Slicer and Use Measure:
Add the DateSelection column as a slicer to your report and use the SelectedDateMeasure in your visuals.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
@LearnToFly You have basically 2 options. You can create multiple relationships and then use USERELATIONSHIP in measures or you can create 3 separate calendar tables. OK, a third option is to just ditch the calendar table and just straight use the date columns in the fact table but some folks would frown on that approach although it would work perfectly fine for the majority of scenarios.
Thanks, Greg, I did try to do the USERELATIONSHIP option however I also needed to slice on it. Therefore, I guess my only option is multiple calendars...I just really haven't seen others using that approach but will try it out of necessity. Thanks for the help!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
5 | |
2 | |
1 | |
1 | |
1 |