Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I know this has been asked before a long time ago, but maybe there has been an update with a positive answer:
"Is there a way to use a measure in the filter panel?"
I want the user to select the maximum Month-Year to all records of a report, Ideally, it should be possible to select this as a slicer on the 1st page of the report and apply that as a filter to all the pages. Another option would be to use the filter panel.
For example, I'd like to select "2020 - June" or "2020-05" as the maximum month and year, and have the calendar filtered to all the dates equal or less than 30-06-2020.
Solved! Go to Solution.
Hi @webportal ,
I just dislike the fact that it displays the lower bound - since I'm filtering values equal to or lower than, this should not be visible.
You could insert a rectangle to hide the minimum date of slicer.
if the slide control is visible, unexisting months are displayed which is nonsense:
This is by design. For the befor slicer or after and between slicer, it will show the continuted date.
You cannot use a measure in the fitler pane as the thing to be filtered because a measure only ever has one value. It just shows different values depending on the filter context where it is used, so different rows in a Table visual, or bars in a bar chart, etc. You would need to add a column in Power Query if possible, or a Calculated column in DAX to filter off of that. Power Query is usually your best bet, but this is one of those times where a calculated column could be the optimal solution.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting"You cannot use a measure in the fitler pane as the thing to be filtered because a measure only ever has one value"
Sure, I'd like a filter to be a column whose values are lower than or equal to ... the current value of the measure!
That is what I am saying. A measure by itself has no value. It only gets a value by filter context. Putting a measure in a slicer or filter provides no context, so the measure has no value. "Current Value" only applies when filters are applied, and filter context comes from how it is used in a visual. And even then, the measure only has 1 value. If it is on 30 rows of a table visual, then the measure has 1 value calculated 30 different times for each row of the table visual, not 30 values. There is no "current value." It isn't like a formula in Excel. Measures just float in space until you put them in a visual, then they get to work.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk, maybe I didn't explain myself properly.
1 - Say, we have a slicer with integers 201901 to 202012 to represent year - month. This is a column in the calendar table -> force single selection
2 - We create a measure to get the the last date of the month / year selected like: DateMax = MAX('Calendar'[Date])
This measure would still have a value even if no month had been selected in the slicer (thus, the filter context, in this case, is the whole calendar table). But, since we're forcing the user to select a year - month, it would always get the last day of the respective month and year.
The objective of my question is: How can we filter the calendar table in all pages of the report in order to retrieve only the rows of column 'Calendar'[Date] to be less than or equal to the value of the measure DateMax?
Hi @webportal ,
@edhans is right. We can't drag measures into "Filters on all pages". It is supported to use measures in "Filters on this visual". You could create a measure like below to retrieve the slicer and then sync it to all pages as eadhans suggests.
DateMax =
VAR DateMax = MAX('Calendar'[Date])
RETURN
IF(MAX('Calendar'[Date]) <=DateMax,1 ,0)
(Please modify it based on your actual situation.)
Sync and use slicers on other pages
Thanks @v-xuding-msft and @edhans
I see the answer to my question is still no, so I'm using a slicer and synhc it to all pages.
I just dislike the fact that it displays the lower bound - since I'm filtering values equal to or lower than, this should not be visible. Also, if the slide control is visible, unexisting months are displayed which is nonsense:
Hi @webportal ,
I just dislike the fact that it displays the lower bound - since I'm filtering values equal to or lower than, this should not be visible.
You could insert a rectangle to hide the minimum date of slicer.
if the slide control is visible, unexisting months are displayed which is nonsense:
This is by design. For the befor slicer or after and between slicer, it will show the continuted date.
@webportal - you can control what the slicer shows. My Date table as a number of IsSomething fields that evaluate to true/false. IsFuture, isThisWeek, IsThisYear, IsLastWeek, etc. You could set up a custom column in your date table that would evaluate to TRUE for you (IsLast6Months for example), then filter that slicer by that true/false field.
Please mark one of the posts here as a solution so everyone will know that the topic is closed and others can find the same answer.
Thanks!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
The objective of my question is: How can we filter the calendar table in all pages of the report in order to retrieve only the rows of column 'Calendar'[Date] to be less than or equal to the value of the measure DateMax?
Why not copy the slicer on the first page to all pages of the report? Sync the slicers, then hide the slicer on all pages but the first page of the report?
So the first page has the slicer. User selects 201904. All copies of the slicer now have the same value, and every page of the report with that hidden slicer are filtering the Calendar table to be 201904.
This is a common usage of syncing and hiding slicers. I have a report where I want the entire report to be filtered by a customer if chosen from a Customer Number slicer, but the slicer is only visible on page 1. All other pages it is sync'd and hidden.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |