Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pelowski
Helper III
Helper III

DAX - 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.

1 ACCEPTED SOLUTION
pelowski
Helper III
Helper III

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. 😁

 

AlternativeOption.png

View solution in original post

7 REPLIES 7
pelowski
Helper III
Helper III

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. 😁

 

AlternativeOption.png

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
Super User
Super User

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.

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.

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.

AlexisOlson_0-1633107498234.png

 

 

Yeah, but if I don't want anything to show in the table (because it's not relevant until something is selected on the matrix) I have to do something like this which I've done but it comes with a lot of headaches like...

  1. It's hard to edit the report because there is a transparent card in the front of the table all the time.
  2. I have to allow users to see the scrollbar at all times otherwise they can't select it because the transparent card visual is in the foreground.

 

As I said, this is a really basic example.  What I was hoping to do is take the data from the matrix visual, filter a table with one unique value (email address in the real world) and then pivot on something else entirely while joining in another table of values to calculate some totals.  I've defaulted to doing it all in PowerQuery which I'm comfortable with, but I was hoping that more of it could be done on the DAX side so filter context could be maintained.

 

This is what I have...

Card in front of table that becomes transparent when email address is selectedCard in front of table that becomes transparent when email address is selected

I see. I can't think of a better solution at the moment.

 

A different approach would be to make a dropdown email slicer (that doesn't filter the left matrix) with a forced single selection. This has different drawbacks but might work in some similar situations.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.