Dynamic page filter based on attribute's value

[Anonymous]

I have in my report two tables. One table is a long table with a lot of data, and another table that is a single row, containing some other information. The two tables are linked together with a one-to-many relation in the model tab.

In one page of my report, I want to filter the data in the visuals (which use the data from the big table) with respect to the field of the relationship of the small table. It is important to note that this field changes in time.

I set a new page filter with respect to the wanted field, but whenever the value changes, the filter doesn't follow and keep selecting the initial value (of when the filter was set). I would like the filter to adapt to the new value of the field automatically.

Is there any way to do this ?

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I think you can achieve this using measure and visual filter, then I created some simple data to make a test, you can try my steps:

This is my test data:

I create a measure in the ‘Big table’:

Flag =

var _month=MAX('Small table'[Month])

return

IF(MAX('Big table'[Month number])=_month,1,0)

Then I created a table chart and place it and apply a filter like this:

And you can get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Thanks a lot for the detailed answer.

I think what you propose is close to what I want, except that I have some troubles understanding this line :

IF(MAX('Big table'[Month number])=_month,1,0)

In particular, what is the purpose of the MAX() aggregation in this formula? I don't understand why an aggregation is needed here.

NOTE : Multiple rows in the big table should match the attribute of the small table. I don't think this changes something in your answer.

[v-robertq-msft]

Hi, @Anonymous 

When we use the max function like this in a measure, we can get the column value of the current row, so (max ('Big table '[month number]) is to get the value of [month number] of each current line of visual chart obtained here.

In other words, this MAX() function acts as an aggregation, but it only targets the current line. Do you understand what I mean?

More info about the MAX() function

Thank you very much!

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Alright I see, thanks for the explanation.

Now about the behavior of this : It's actually kind of weird because it doesn't give the same result as applying a page filter (as explained in the initial question) whereas conceptually for me this should do the same.

It appears the page filter filters more, meaning that there are more rows with your solution than with the page filter (and the right solution is with the page filter : I compared the data with what's on the database.)

Could it be a problem of filtering / computation order ? It also appears that for some visuals it works fine and for some others it doesn't...

If you have any idea of what's happening that would be great, otherwise I guess I'll continue investigating on my own since I cannot upload any of the data in here to illustrate specifically the problem.

Thanks

[v-robertq-msft]

Hi, @Anonymous 

As far as I’m concerned, the method I used works on the visual filter, and I have never tried this in the page filter. I think you can try to apply the visual filter for each visual within your page and check if all the visuals have been correctly filtered.

If not, you can tell me what kind of visual failed to be filtered, and I will also do some research.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Actually it appears your method cannot be applied as a page filter.

Anyway I applied it as a visual filter on each visual of the page. Here are two pictures showing the difference with your method and the initial page filter I mentioned (graying out sensible information) :

Here is with your method (light blue is a Max and dark blue is a Min): 

and here is with the page filter :

(the y scale didn't change from one image to the other) So this confirms the difference. And to answer your other question : Actually I think it fails on all the visuals except Tables.

[Anonymous]

Anyone ?

[amitchandak]

@Anonymous , Not very clear, Are you looking for a parameter page

refer Need Traditional Parameter Page: https://youtu.be/F3qMwQtWjvo

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

Let's say the big table contains data about sales of an entire year, and the other table contains a single attribute being a certain month.

In one page of my report I want to see in the visuals only the data of the big table about the month which is in the small table. The month in the small table is dynamic and changes over time (it might be February, and 15min later become August, for instance.)

I want the page filter to automatically take into account that the month of the small table changed and adapt the visuals.