cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asjones Regular Visitor
Regular Visitor

Advanced Filtering - Dynamic Filter Based on Field Value

I would like to do an Advanced Visual Filter and/or page Level Filter based on a field in a table/record with single row (thus filter on a field).  Example the table might have a field called [table]day = 14.  The Advanced filter would filter on values  <= [table]day.   That in one case might be 14 and then later 16 etc.

 

However the advanced filter contains/is/greater than/less than/etc. only look to be able to format to work on hard coded values.

 

Any thoughts?

 

thanks

 

Alan

4 REPLIES 4
Super User
Super User

Re: Advanced Filtering - Dynamic Filter Based on Field Value

It's not really clear what you want. Tables have columns and rows.  Do you mean you want to filter a column called table[day] <=14 ?  If the column only has a single value/row, why do you need this filter?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
asjones Regular Visitor
Regular Visitor

Re: Advanced Filtering - Dynamic Filter Based on Field Value

@MattAllington

 

 

 

Sorry for the delay in responding. I realized I did not add enough detail to my original post (normally I go into to0 much detail).  I have multiple tables (at least 2). I have started in a few cases having a an extra table generated that has information about my data that was exported from our ERP system. It has a single row and a column for each item. It has columns on the date/time the data was pulled, what environment (test vs production), various parameters used in the export of the data form our ERP to the file Power BI will use to import the data. Some of I use as my own checks and reminders. Although displaying dates/times to the user has been helpful to the consumers of the report.

 

So I have my normal "data tables" and this "parameter table".  Still don't have a good name for it.

 

I would like to do an Advanced Visual Filter and/or page Level Filter based on a field in the parameter table/record (thus filter on a field from one table to affect display of another).  Example the parameter table might have a field called [table]day = 14.  The Advanced filter would filter on values  <= [table]day.   That in one case might be 14 and then later 16 etc.  So the data table would only display stuff on or before a day passed to a filter from a parameter in another table.

 

 However the advanced filter contains/is/greater than/less than/etc. only look to be able to format to work on hard coded values.

 

 Thanks for your time and help on this

 

Alan

 

 

 

Super User
Super User

Re: Advanced Filtering - Dynamic Filter Based on Field Value

Write a measure to "harvest"  The selected date from your parameter table 

 

selection = max(parametertable[date])

 

 Then write to measure that will return a value for any date greater than its harvester measure 

display = calculate(countrows(data),filter(data,data[date] <= [selection]))

 

place the data you want to see in a table along with this measure. I think it will work. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
itsdeblo Visitor
Visitor

Re: Advanced Filtering - Dynamic Filter Based on Field Value

Hi MattAllington,

 

You are definitely a genius!!! This works brilliantly. Thanks ever so much.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)