Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Team,
How to have a common date slicer for three date fields in the same table.
The Year slicer can filter MOTREF1,MOTREF2 and MOTREF3.
Any suggestions?
Thanks
Solved! Go to Solution.
Hi @Anonymous
You can create a calculated column in Calendar table . Use FORMAT dax to convert the date format into year and month format, and then add a slicer, put this column in the slicer, keep the original relationships, no need to change, the data in the data table will be changed according to your filtering .
I made a little change to the original data for easy comparison .
Calculated column : Column = FORMAT('Calendar date'[Date],"mmm-yyyy")
The effect is as shown
The final result is as shown
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
What is the result, can you achieve your needs?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards
Community Support Team _ Ailsa Tao
Hi @Anonymous
You can create a calculated column in Calendar table . Use FORMAT dax to convert the date format into year and month format, and then add a slicer, put this column in the slicer, keep the original relationships, no need to change, the data in the data table will be changed according to your filtering .
I made a little change to the original data for easy comparison .
Calculated column : Column = FORMAT('Calendar date'[Date],"mmm-yyyy")
The effect is as shown
The final result is as shown
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot!
Amazing and thanks! Will be right back after trying!!!
Hi @Anonymous
You need create a calendar table first and then create relationships between the calendar date and three dates in data table .
(1)Create a calendar table like this, and use the field as a slicer .
Calendar date = CALENDAR(DATE(2021,06,01),DATE(2021,06,10))
(2)create relationships between the calendar date and three dates in data table.
Create an active relationship between 'Table'[MOTREF1] and 'Calendar date'[Date], two inactive relationships between 'Table'[MOTREF2] and 'Calendar date'[Date] , 'Table'[MOTREF3] and 'Calendar date'[Date] .
Then create measures by USERELATIONSHIP DAX to activate the inactive relationship . But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.
(3)Create measures with USERELATIONSHIP dax .
MOTREF2 = CALCULATE(SUM('Table'[Value]),USERELATIONSHIP('Table'[MOTREF2],'Calendar date'[Date]))
MOTREF3 = CALCULATE(SUM('Table'[Value]),USERELATIONSHIP('Table'[MOTREF3],'Calendar date'[Date]))
(4)Add the measure in visual then the relationships can be activated .
The effect is as shown
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ailsa,
Is there a way to have Year and months rather than individual dates ?
Thanks
Thanks a lot ! Will try and be right back.
Hello Amit,
I have created a Calendar table :
Not sure what to do next in terms of relating the [Date] : CalendarAuto [Date] and MOTREF1,MOTREF2,MOTREF3
Thanks
Thanks, Amit, will try and be right back.
@Anonymous , join them with a common date table. Only one join will be active. rest you can activate using userelationship
refer my blog for more details
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |