Forum Discussion
Table Visualization not filtering as expected
I have two queries that I'm joining in Power BI Desktop (many to one, cross filter direction is "single"). I have created a table visualization that displays some data from both tables. I am trying to filter on blanks in "Field A". When I try this my table isn't being filtered, but rather all non-blank values in "Field A" are disappearing. Has anyone run into this sort of behavior before?
While I still don't understand the filtering behavior I previously described, I just introduced myself to "Merge Queries" in the Power Query Editor. Performing a "Merge Queries as New" with a left outer join (Table A is the left table), I end up with a merged table where I can set a visual level filter of "'Merge1'[Table B.GUID] is blank" and I'm left with the 90 GUIDs from Table A that don't have a match in Table B.
Understanding the filtering behavior I originally posted about would be great (maybe it's just a misunderstanding on my part), but the "Merge Queries" functionality seems to be a nice alternative to what I was initially trying to do.
5 Replies
Hi RichWilcox,
I tested on my side, the table visual could be filtered by both blank and non-blank values. Please share sample data of the two tables together with their relationship so that I can try to reproduce your scenario.
Regards,
Yuliana Gu
It would be nice if I could just share a sample/test pbix file on this post (maybe there is a way and I just don't know how). At any rate, here are some screenshots and some additional explanation of what I'm doing and the issue I'm encountering:
I have Table A and Table B which are related to each other like so:
As you can see in the next screenshot, there are 1145 GUIDs in Table A, 90 of which are not found in Table B:
By applying the following filter, I am trying to isolate the 90 GUIDs in Table A that are not found in Table B:
Instead of getting a filtered table visualization that only displays 90 rows (i.e. the 90 GUIDs in Table A with no matching GUID in Table B), I get the same 1145 GUIDs in Table A, but all GUID values from Table B dissappear:
If there is a more appropriate approach to getting a filtered table visualization that only displays the rows where the Table B GUID is blank, I'd love to know how to do that.
To share a file, we usually ask users to upload the PBIX to something like Dropbox or OneDrive and post the link.
