Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all together!
My following question is based on this post here: Always select Blank in the Filter. Unfortunately, it does not fully cover my problem. My minimum working example is based on this for better comparability and understanding. Since I am still new to the forum here, I hope it is correct to create a new issue...
But now to my concern:
I would like to cross-filter my table so that a selected option in the slicer also includes all blank values (i.e. and-condition).
I have a table called 'Table' that contains the columns 'Year', 'Season' and 'Value':
As you can see, the columns 'Year' and 'Season' contains both, data as well as nulls. I would like to filter this table by 'Year' and 'Season' using two different slicers. Therefore I created two seperated Tables called 'Seasons' and 'Years', that only contain the respective columns 'Year' resp. 'Season':
To ensure that all blanks are selected in addition to the selected option in the slicer, I have adopted the measure from Always select Blank in the Filter
Season_Measure = IF(SELECTEDVALUE('Table'[Season]) in ALLSELECTED(Seasons[Season]) || SELECTEDVALUE('Table'[Season])=BLANK(),1,0)
Year_Measure = IF(SELECTEDVALUE('Table'[Year]) in ALLSELECTED(Years[Year]) || SELECTEDVALUE('Table'[Year])=BLANK(),1,0)
If there is no relationship between the tables, everything works as expected:
BUT: If I select the year 2021 as shown in the screenshot, then I only want the seasons that exist for this year to be displayed in the season slicer (in this case only Spring, Winter and Fall). However, if I create a relationship between the tables, the measure no longer works, which also shows me the blanks.
I very much hope that I have described my problem sufficiently and concisely. I am very happy to receive help!
Best regards, Felix
Solved! Go to Solution.
Hi @ms_flxx
You can add a table like below. Then use these two columns in the slicers. Modify the measures to reference this new table's columns.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi @ms_flxx
You can add a table like below. Then use these two columns in the slicers. Modify the measures to reference this new table's columns.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |