Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
webportal
Impactful Individual
Impactful Individual

Is there a way to use a measure in the filter panel?

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.

1 ACCEPTED 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. 

1.gif

 

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. 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
AntrikshSharma
Community Champion
Community Champion

Yes, you can add measure under "Add data fields here" and then add some restrictions based on the measure's value.
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
webportal
Impactful Individual
Impactful Individual

"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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
webportal
Impactful Individual
Impactful Individual

Ok, 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.) 

8.PNG

 

Sync and use slicers on other pages 

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

Anotação 2020-07-07 101001.jpg

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. 

1.gif

 

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. 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ok, it's not perfect, but it works.
Thanks for your help!

@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!

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.