dynamic table filter using slicer selection

[pavithra12]

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!

[pavithra12]

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_Deckler]

Just a guess, perhaps try:

CALCULATETABLE( DISTINCT( Division[DIVISION] ), FILTER( ALL(Division), Division[CROP_YEAR] = Crop_Year[CropYear_Sel] ) )

[pavithra12]

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.

[Greg_Deckler]

Can you provide some sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[pavithra12]

This is the crop_year table,

This is Totals table,

And this is the division table:

As you can see, division '2-central Divsion' is available only for the crop_years 2014,2015 and 2016. So If I select the crop_year as 2017 from the slicer, the totals value (second image)  for 2017 should be displayed in a table and the division for 2017 should be displayed in a seperate table (that is 2-central Divsion' should not be displayed under division).

Is there a way in which I can achive this? 

[Greg_Deckler]

I'm actually not seeing whatever images? you posted. Can you post some text so that it can be copied and pasted into a data model and then experimented upon?

[pavithra12]

Crop_Year table:

2015

2016

2017

2018

Totals table

2016    11    49   819   AL   2250

2017    11    49   819   PA   2250

2017    11    49   819   HP   2250

2018    11    49   819   HP   2250

2015    11    49   819   CM   2250

Division Table

2-Cental Division     2014

2-Cental Division     2015

2-Cental Division     2016

1-Mid Division         2017

So when crop_year 2017 is selected from the slicer (using crop_year table),

The totals table should display the 2017 data (2nd and 3rd row) and even the division table should display the 2017 data (4th row).

Thanks! 

[riteshgaur]

Hi Pavithra

I saw your post ... were you able to solve this issue?

I have landed up in same bucket now. 

[Anonymous]

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?