Forum Discussion
dynamic table filter using slicer selection
Here is my case,
I have three tables, crop_year,division and totals.
Totals is filtered based on crop_year and division. Divion is filtered based on crop_year, but as you can see due to circular dependency I am not able to establish a relationship between crop_year and division.
So, in order to filter the division based on the selected crop_year (for example 2017 has a different set of division and 2017 has a different set), I created a measure called as 'CropYear_Sel' in the crop_year table to capture the dynamic value selected by the user.
CropYear_Sel = var selection = SELECTEDVALUE(Crop_Year[CROP_YEAR],0000) return selection
And I wanted to use this measure to dynamically filter the division values, but when using the measure the result is null
CALCULATETABLE( DISTINCT( Division[DIVISION] ), FILTER( Division, Division[CROP_YEAR] = Crop_Year[CropYear_Sel] ) )
But, when the crop_year is hard coded I am able to get the filtered divisions using the below mentioned DAX
CALCULATETABLE(DISTINCT(Division[DIVISION]), Division[CROP_YEAR] = 2017)
Is there a solution for this? Any help would be appriciated! Thanks!
9 Replies
- pavithra12Regular Visitor
Here is my case,
I have three tables, crop_year,division and totals.
Totals is filtered based on crop_year and division. Divion is filtered based on crop_year, but as you can see due to circular dependency I am not able to establish a relationship between crop_year and division.
So, in order to filter the division based on the selected crop_year (for example 2017 has a different set of division and 2017 has a different set), I created a measure called as 'CropYear_Sel' in the crop_year table to capture the dynamic value selected by the user.
CropYear_Sel = var selection = SELECTEDVALUE(Crop_Year[CROP_YEAR],0000) return selection
And I wanted to use this measure to dynamically filter the division values, but when using the measure the result is null
CALCULATETABLE( DISTINCT( Division[DIVISION] ), FILTER( Division, Division[CROP_YEAR] = Crop_Year[CropYear_Sel] ) )
But, when the crop_year is hard coded I am able to get the filtered divisions using the below mentioned DAX
CALCULATETABLE(DISTINCT(Division[DIVISION]), Division[CROP_YEAR] = 2017)
Is there a solution for this? Any help would be appriciated! Thanks!
- Greg_DecklerCommunity Champion
Just a guess, perhaps try:
CALCULATETABLE( DISTINCT( Division[DIVISION] ), FILTER( ALL(Division), Division[CROP_YEAR] = Crop_Year[CropYear_Sel] ) )
- pavithra12Regular Visitor
Hi Greg!
Thanks for the reply, but that doesn't work either.
I am quite not sure if I can create a dynamic calculate table or calculate column depending on user selections. The DAX function with a hard coded value if working fine but when using the measure it does not return data.
- AnonymousNot applicable
I have a similar problem. when I try to use a measure as an argument to the filter function, it does not give any results. It will not be a valid argument then?