October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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!