I need to dynamically create a table with rows filtered from another table

[BelgiumRoland]

Hi Folks,

I'm new to DAX and am struggling with the following problem.

I have a table with years from 2011 until 2021.

I need to create a subset of that table with only the rows where the year is less or equal than a value chosen by the user.

Like this:

If I change the Chosen Year to 2017, I should have one more row in my Filtered Years table, if I change it to 2015, one row less.

I tried the CalculateTable function with a Filter clause based on a SelectedValue from a slicer, but as those tables are calculated at Refresh time, it didn't do what I needed.

Currently, the Chosen Year value is the result of the selection in a slicer, but I don't care how it's done, as long as it's selected by the user consuming my report.

Thanks

[BelgiumRoland]

Thank you Valteri,

That does what I asked for but that wasn't the whole story.

From those 10 years, what I want is the user to select one and then keep the 5 years from that one down.

I have another table called Values with CompanyIDs and Years and for each a value.

What I want is show that in a matrix with the companies in the rows and the 5 years in the columns.

Basically the goal is to show up to 5 years of data, letting the user select the last of those years.

I was able to get what I wanted as far as the 5 years where concerned with the following calculated tables:

Get all the years before a chosen year:

SomeExercice = CALCULATETABLE(Exercice, FILTER( (Exercice), Exercice[ID] <= 1415))

Get the 5 years from the maximum of that table:

5Exercices = TOPN( 5,someExercice, SomeExercice[ExYear] , 0 )

I added a relation between my Values table and the 5Exercices table and that filtered the data the way I wanted.

But it wasn't dynamically defined by the report user, so I was trying to build the "SomeExercice" table dynamically.

I implemented you solution like this:

BeforeSelection Slicer = IF(countrows(FILTER('Exercice', Exercice[ID] <= MAX('Exercice 4 Filter'[ID])))>0,1,0)

where 'Exercice 4 Filter' is a duplicate of the Exercice table to have a table that is not connected with a relationship and it's the one I use in the slicer.

Now that my Excercice table is filtered, I made a relation between it and my Values table, but the years in that one are not filtered.

I'm a bit lost at this point.

[ValtteriN]

Hi,

For the past 5 years dynamically you can modify the filter formula a bit:

BeforeSelection Slicer = IF(

countrows(FILTER('Calendar','Calendar'[Year]<MAX('Visual Calendar'[Year])

&&'Calendar'[Year]>=MAX('Visual Calendar'[Year])-2

))>0,1,0)

In this example I am getting the past 2 years, but the logic is same:

[BelgiumRoland]

Thank you Valtteri,

That does indeed limit the filtered years to 5 but I can't filter my data based on those 5 years.

I still see all years even though the Exercice table is filtered and in a relationship with my data table.

I'm running out of time, so I'll just limit the number of years that I retrieve from my DB. Making that dynamic and in the hands of the user will have to wait.

[ValtteriN · Accepted solution]

Hi,

You can create this kind of Filter measure and apply it to your visuals:

BeforeSelection Slicer = IF(countrows(FILTER('Calendar','Calendar'[Date]<MAX('Visual Calendar'[Date])))>0,1,0)

End result:
Note that my slicer date is from 'Visual Calendar' and there isn't relationship between that and 'Calendar'

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!