Forum Discussion

pavithra12's avatar
pavithra12
Regular Visitor
7 years ago

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

  • pavithra12's avatar
    pavithra12
    Regular 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_Deckler's avatar
    Greg_Deckler
    Community Champion

    Just a guess, perhaps try:

     

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

    • pavithra12's avatar
      pavithra12
      Regular 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.

       

       

      • Anonymous's avatar
        Anonymous
        Not 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?