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
Europe Fabric Conference

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors