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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MRD87
Frequent Visitor

Use the max value of a filter to filter a visual

Hello all,

 

I have spent a lot of time in figuring out a solution for this. 


I am using a filter where I select a week. I want the visual to not show the results after the max date of the selected week. 

So instead of this

 

MRD87_1-1664186440439.png

 

 

I want this result

MRD87_2-1664186552986.png

I have a revenue and a date table which are related by date.  I hope someone can help me figure this out. 

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create a copy of your date table just for use in the slicer

Slicer Date = SELECTCOLUMNS( 'Date', "Date", 'Date'[Date], "Week", 'Date'[Week])

Do not link this table to any other tables. Then you can create a measure like

Filtered Revenue = IF( MAX('Date'[Date]) <= MAX('Slicer date'[Date]), [Revenue CY])

and use that in your visual

View solution in original post

10 REPLIES 10
MRD87
Frequent Visitor

Thank you this helped me out tremendously

johnt75
Super User
Super User

Create a copy of your date table just for use in the slicer

Slicer Date = SELECTCOLUMNS( 'Date', "Date", 'Date'[Date], "Week", 'Date'[Week])

Do not link this table to any other tables. Then you can create a measure like

Filtered Revenue = IF( MAX('Date'[Date]) <= MAX('Slicer date'[Date]), [Revenue CY])

and use that in your visual

MRD87
Frequent Visitor

Slicer Date = SELECTCOLUMNS'Date Slicer'"Date"'Date Slicer'[Date]"Week"'Date Slicer'[Week])

 

I get the following error:

 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

You need to add it as a New Table, not a column or a measure

MRD87
Frequent Visitor

Thank this brought me a little bit further in the right direction. I am almost there. I had to change the <= into >= for the measurements to work. Now I get the right weeks, but every week shows the revenue of the selected week ( week 23 in this case). Is there a solution for this?

Thank you in advance

 

MRD87_0-1664356738186.png

 

what code are you using for the Filtered Revenue measure ?

MRD87
Frequent Visitor

Filtered Revenue = IF( MAX('Date'[Date]) >= MAX('Slicer date'[Date]),[Revenue CY])

that should be <= not >=, and you need to use the week from your date table, not the slicer date, on the column chart

MRD87
Frequent Visitor

Thank you this works, however I have one last challenge.  I want both datetables to be filtered with one slicer.  This is not possible with a relation because that would nullify the result. Is there a way to filter both slicers with the same value (week)?

If a filter were applied to the date table that would break the calculation that we have working now.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.