Dynamic table by filter

[Syndicate_Admin]

Good afternoon;

I have a problem with generating a filter to a filter according to the value entered in the primes slicer.

Explain:

I have a slicer where I filter the variable "Years" and I have another slicer with the variable Name.

The idea is that when applying the first filter example "year 2018" in the second filter only show me the names that are active in that year or that do not have an inactive date.

filter year	2018
number	active	year
Juan	No	2019
Louis	Yes
Sebastian	Yes
Gloria	No	2017

The result for the second slicer would be Juan, Luis, Sebastian.

If the year selected in the first slicer was 2020 the result in the second slicer would be Luis and Sebastian. Or if it were 2016 it would show all the names.

Try to get the selected year using the VALUES fuintion to capture the selected value and create a table where the comparison with this value is made but it generates error.

The idea is that in the end the second slicer will filter the visualization of a metric.

Thank you for the help you can give me.

[v-yalanwu-msft]

Hi, @Syndicate_Admin ;

So I would like to know whether my method has solved your problem? If not, can you provide more details?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

I don't understand the question. since your option was the one I chose as a solution.

[v-yalanwu-msft · Accepted solution]

Hi, @Syndicate_Admin ;

You could create a flag measure. （ps: you should have other year table as a filter and have no relationship with main table)

Then create a measure.

flag = IF(MAX('Table'[year])>=MAX('filter'[Year])||MAX('Table'[year])=BLANK(),1,0)

Then apply it into filter.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good day;

With respect to the year this value is not mandatory and this identifies when the name has ceased to be active, that is, Juan ceased to be active in 2019 and luis and Sebastian are still active for that reason they do not have the variable year completed. That is why if 2019 is selected, you must filter those that have a year higher than 2019 and those that do not have a year, that is, empty.

[amitchandak]

@Syndicate_Admin , first of all do not see year in all the row, That should be there. If nor then use fill up or down in power query

you can create a measure like

calculate(count(Table[Name]), filter(Table, Table[Year] = selectedvalue(Table[Year])))

use that as visual level filter for Name

[Syndicate_Admin]

After analyzing this option although it works, it is time to modify "the equality" of the filter to "greater or equal" and the records that do not have a date have to be filled with an extreme date.

The advantage it has is that you do not have to create a data table of years as suggested to me in the other option.