Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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!
Just a guess, perhaps try:
CALCULATETABLE( DISTINCT( Division[DIVISION] ), FILTER( ALL(Division), Division[CROP_YEAR] = Crop_Year[CropYear_Sel] ) )
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.
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
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?
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?
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!
Hi Pavithra
I saw your post ... were you able to solve this issue?
I have landed up in same bucket now.
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?
User | Count |
---|---|
93 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |