Forum Discussion
Helper IIIDAX - Simple Table FILTER not working with SELECTEDVALUE
Here is a video showing what I'm trying to do...
https://www.screencast.com/t/ii7Hcx8UlwT7
This is the simple code that I can't get to work.
Table Filtered = FILTER('Table', 'Table'[Color] = SELECTEDVALUE('Table'[Color]))I've also tried the following.
Table Filtered = FILTER('Table', 'Table'[Color] = IF(HASONEVALUE('Table'[Color]), VALUES('Table'[Color]), BLANK()))
Table Filtered = CALCULATETABLE('Table', FILTER(ALL('Table'), 'Table'[Color] = SELECTEDVALUE('Table'[Color])))
I'm at a total loss. Any help you can provide would be greatly appreciated.
The sample PBIX I used for this example can be found here.
My colleague found a good solution to this problem. It's definitely a work-around but it's better than the workaround I was using.
Measure Code:
SelectedEmail = IF(HASONEFILTER('Upgrade Details'[Email]), 1, BLANK())Then the Measure is added to the very end of the table, renamed to "_" (so it takes up minimal space) and the column is reduced so it can't be seen. The other columns have to be set to not aggregate. Now, the table on the right will only show data if a single email value is selected on the left. 😁
7 Replies
- pelowski
My colleague found a good solution to this problem. It's definitely a work-around but it's better than the workaround I was using.
Measure Code:
SelectedEmail = IF(HASONEFILTER('Upgrade Details'[Email]), 1, BLANK())Then the Measure is added to the very end of the table, renamed to "_" (so it takes up minimal space) and the column is reduced so it can't be seen. The other columns have to be set to not aggregate. Now, the table on the right will only show data if a single email value is selected on the left. 😁
- AlexisOlson
Super User
Not bad.
Note that you can resize columns to make them effectively invisible (though you may need to turn off word wrapping on columns and values for it not to stretch the rows).
- AlexisOlson
A calculated table cannot be responsive to filters.
Calculated tables and calculated columns are only calculated once per time the data model is loaded or refreshed and not in response to interaction with various slicers or filters on a report page.
Measures, on the other hand, are designed to be dynamically responsive to interactions and there's usually a way to use them to do what you ultimately intended to use a calculated table for.
- pelowski
Okay, thanks. I didn't realize these didn't refresh based on filter context.
But this can't exist as a measure right? I'll get a scalar value warning because I'm returning multiple columns. I want the filtered table... I'm not sure how that can be represented in a measure.
- AlexisOlson
You don't necessarily even need measures for this simple case since visuals can cross-filter each other and you can create slicers.
As an example, try selecting Black in your bottom matrix visual and see what it does to the table visual above.
