Forum Discussion
Slicer for several columns
Hi everyone,
I have found several posts with people with the same question, but I couldn't find a post which suits my issue.
I have created 4 calculated columns in my date table:
- Last week (with the text 'Last week' or a blank)
- Last month (with the text 'Last month' or a blank)
- Last quarter (with the text 'Last quarter' or a blank)
- YTD (with the text 'YTD' or a blank)
In which way can I combine those 4 calculated column in one to create a slicer for this?
Hi, Anonymous ;
Unfortunately, Power BI accepts measures as filters only at a single visual level, and it does not accept measures as filters at the page level nor at the report level. so you only
Another method is unpivot in power query .
1.select 4 column then unpivot.
2.removing empty value.
3. create two relationship.(both not single)
Then you could use second table's value to filter on all pages.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
Anonymous , these are overlapping, You can not have them as column in date table.
You need to create a new table with these values and based on selected value you need to choose a measure
refer who measure slicer works
measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-patternor
calculation group
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/
In case you want QOQ, YOY, WOW
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Thanks amitchandak !
I created 4 measures for each calculated column by using the function Firstnonblank. So, for example, for the column YTD, the measure is as follows:YTD = FIRSTNONBLANK(Date[YTD], "YTD")
I did this also for Last week, Last month and Last quarter.
The formula to select one of those 4 is as follows:Selected value = Switch(SELECTEDVALUE(Period[Measure name], "YTD"),"YTD", [YTD],"Last week", [Last week],"Last month", [Last month],"Last quarter", [Last quarter ])
If I create a table with the column Date and with the measure Selected value, the slicer Measure name works. But when I don't add the measure Selected value or when I add a random measure or column, the slicer doesn't do anything anymore...
How can this be solved?
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
Can you share a simple sample to explain the problem in more detail and the desired output?
It makes it easier to give you a solution.
Looking forward to your reply！
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yeah sure!The date table with the relevant columns for this issue is as follows:
I want to have these four columns merged together, so I can add a filter here:
As you can see, I had to add those 4 columns seperately in the filter section. But I want to add one column/measure where the user can choose between: YTD, Last week, Last month or Last quarter.
I am not sure if this is possible, because you have to add a column in this filter section and like amitchandak already mentioned: the columns are overlapping.
And the following measure does wurk:Filter Measure =
VAR _Slicer = SELECTEDVALUE(Period[Measure name])
Return
INT(
SWITCH(TRUE(),
_Slicer = "YTD", SELECTEDVALUE(Date[YTD]) = "Current year",
_Slicer = "Afgelopen week", SELECTEDVALUE(Date[Last week]) = "Last week",
_Slicer = "Afgelopen maand", SELECTEDVALUE(Datum[Last month]) = "Last month",
_Slicer = "Afgelopen kwartaal", SELECTEDVALUE(Datum[Last quarter]) = "Last quarter",
1
)
)
But I can only apply this measure on 'Filters on this visual' and not on 'Filters on all pages'. And it doesn't work for all visuals. Which makes sense, since the measure only works when I add the named columns in the formula.
I hope this makes clear what I am looking for. And if not, you're welcome to ask!
- v-yalanwu-msft
Hi, Anonymous ;
Unfortunately, Power BI accepts measures as filters only at a single visual level, and it does not accept measures as filters at the page level nor at the report level. so you only
Another method is unpivot in power query .
1.select 4 column then unpivot.
2.removing empty value.
3. create two relationship.(both not single)
Then you could use second table's value to filter on all pages.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you v-yalanwu-msft !
I had to change my calculated columns to columns created in the Edit Query to be able to apply these steps.
After I had done that, it was really easy to follow your steps 🙂
