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.
I have a dataset and report showing data for weekly events. My date dimension table has a column designating week type for each date. There are only three options: normal, Christmas, and Easter. I would like to set up a filter that simply allows the user to include or exclude data from Christmas and Easter weeks. Is there an easy way to achieve this? Thanks!
Solved! Go to Solution.
Hi @pstp ,
Try this:
1. Create a slicer table without any relationships with other tables.
Slicer Table:
SlicerValue |
Include Christmas and Easter |
Exclude Christmas and Easter |
2. Create a measure.
Measure =
SWITCH (
SELECTEDVALUE ( 'Slicer Table'[SlicerValue] ),
"Include Christmas and Easter", COUNTROWS ( Dates ),
"Exclude Christmas and Easter",
CALCULATE (
COUNTROWS ( Dates ),
FILTER (
ALLSELECTED ( Dates ),
Dates[WeekType] = MAX ( Dates[WeekType] )
&& Dates[WeekType] = "Normal"
)
)
)
3. Use the measure as "filters on this visual".
4. Test.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @pstp ,
Try this:
1. Create a slicer table without any relationships with other tables.
Slicer Table:
SlicerValue |
Include Christmas and Easter |
Exclude Christmas and Easter |
2. Create a measure.
Measure =
SWITCH (
SELECTEDVALUE ( 'Slicer Table'[SlicerValue] ),
"Include Christmas and Easter", COUNTROWS ( Dates ),
"Exclude Christmas and Easter",
CALCULATE (
COUNTROWS ( Dates ),
FILTER (
ALLSELECTED ( Dates ),
Dates[WeekType] = MAX ( Dates[WeekType] )
&& Dates[WeekType] = "Normal"
)
)
)
3. Use the measure as "filters on this visual".
4. Test.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is Awesome. I am trying to do something similar.. I have a dataset with partners and their activities essentialy. I want the same idea, where you have a slicer when selected it shows all the partners except a certain partner... Lets say " PartnerName" and when it is not selected it shows all the partners. I was hoping to add it to a few different types of visuals in power bi.
@pstp
You can insert a Slicer on the report and add the week type column which will allow the user to filter as needed.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy That is what I currently have. It has been asked that it change to a single select, where the two options are Include or Exclude. I don't know how to ensure "Normal" weeks are included in both options.
@pstp
You can either change the slicer selection option to allow multi-select or right click on the week type filed and group it in to two categories Normal and Other Two
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |