Forum Discussion
Dynamic page filter based on attribute's value
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 ?
9 Replies
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.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.
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.
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.
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.
