Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Folks ..
I want to create a slicer where the data is sorted by Year,Month,Week,Day wise so there is a default visual in Power BI Iused but the issue is I just want past and present data to be filtered but <refer the img below>
so here i want to control the slicer to show only last and this options excluding next .
How to acheive this or any other possible ways please kindly suggest .
Thanks .
@LivioLanzo @Saud1999 @saud968
Solved! Go to Solution.
Hi @Anonymous
There is no option to limit selections in the generic slicer.
You can create a slicer with similar functionality manually .
Please refer to the linked tutorials :
https://www.youtube.com/watch?app=desktop&v=3SmHtNf0QQ0
https://www.youtube.com/watch?v=fKygF7VEJnQ
https://www.youtube.com/watch?v=JLupF1j49wo
If this post helps, then please consider Accepting it as the solution to help the other
members find it more quickly
Create a New Table:
Go to the "Model" view in Power BI Desktop.
Create a new table by clicking on "New Table" in the "Model" ribbon.
Define the Table with Slicer Options:
Create a table that includes the following values: "Last Month" and "Present Month."
DAX
SlicerTable = UNION(
ROW("DateRange", "Last Month"),
ROW("DateRange", "Present Month")
)
Create a Relationship:
Ensure that there is a relationship between the new "DateRange" column in your slicer table and the appropriate column in your main data table (e.g., date column).
Create a Slicer Visualization:
Drag the "Slicer" visualization onto your report canvas.
Drag the "DateRange" column from your slicer table into the "Field" well of the slicer.
Filter Data with DAX:
Create a measure that uses DAX to filter your data based on the selected value in the slicer.
DAX
FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
"Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
)
Replace 'YourData' with the actual name of your main data table.
Let me know if this helps.
If I have posted a response that resolves your question, please accept it as a solution to close the post.
Thanks for the reply ,
And I apologise for asking silly question , but I'm facing error while creating measure
FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
"Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
)
For this mesaure I got few errors which I tried to resolve but still found new errors
Error : A function 'STARTOFMONTH' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I tried one more :
CurrentDate=Today()
FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]) - 1 && 'CalendarTable'[Date] < STARTOFMONTH(CalendarTable[CurrentDate]))
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]))
)
)
Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Even this also I tried :
StartOfMonth = STARTOFMONTH(CalendarTable[Date])
FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth] - 1 && [Date] < [StartOfMonth])
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth])
)
)
Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Can u please suggest me more ...??
Thanks .
No worries, I am also in a learning phase and not as expert as others but I will try to help you as much as possible.
you have a table named 'CalendarTable' with a column 'Date' and a slicer table named 'SlicerTable' with a column 'DateRange,' you can create the following measures:
DAX
CurrentDate = TODAY()
FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY()) - 1 &&
'CalendarTable'[Date] < STARTOFMONTH(TODAY())
)
),
"Present Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY())
)
)
)
Make sure to replace 'YourData' with the actual name of your main data table. This measure filters your data based on the selected value in the 'SlicerTable' slicer, considering the "Last Month" and "Present Month" options.
Note: The 'YourData' table should have a relationship with the 'CalendarTable' on the date column.
yeah I tried this, its not working .
The error is in STARTOFMONTH() OR CALCULATETABLE() , I'm not getting. Why that STARTOFMONTH() is not accepting today() as parameter ,if we define it as varibale also it is raising same error as I mentioned above . I tried out all possible cases but still the error remains same .
Will it be possible for you to share the PBIX file
Nope , Sorry .
Hi @Anonymous
There is no option to limit selections in the generic slicer.
You can create a slicer with similar functionality manually .
Please refer to the linked tutorials :
https://www.youtube.com/watch?app=desktop&v=3SmHtNf0QQ0
https://www.youtube.com/watch?v=fKygF7VEJnQ
https://www.youtube.com/watch?v=JLupF1j49wo
If this post helps, then please consider Accepting it as the solution to help the other
members find it more quickly
Thanks Bud , those videos helped to proceed in different way .