Retrieve filtered value

Power BI
1

Hi,

I know most people will at first glance think this is straightforward, but I don’t think it is.

I would like to retrieve the value of the filter which is applied on a certain column.

Say you have a table called: “TestTable” with one column “TestColumn”. Then you apply a filter on that column with the value “TestFilter”. I would like to retrieve this “TestFilter” value somehow even if that value is not available in the column. So I’m literally looking to retrieve the filtercontext on that column.

For now, I can only retrieve that value if the value is in fact existing in the column. For example using “selectedvalue”, it would only return a value if “TestFilter” exists in the table.

Any ideas?

2

@wilknsn - can you please send in a pbix with the expected result set so it is easier to provide a solution?

high level i am thinking hasonevalue, selectedvalue, concatenatex and Union - either of these or some combination may be able to get you the test result but a more direct example from you will be very helpful.

3

Say no more: So in the dax measure it should say: “SomeFilter”

image
image1519×764 64.5 KB
Edna - RetrieveFilterValue.pbix (27.9 KB)

4

Hi @Wlknsn

Based on my understanding, what you are trying to achieve is not possible in Power BI. Power BI works on Tables and Columns and if nothing is affected, it can’t retrieve the value from Filter Pane.

Also, SelectedValue is basically sugar coating for HASONEVALUE function. If nothing is filtered, all rows are returned and it will always return blank.

TestHasOneValue =
IF (
    HASONEVALUE ( Customer[Customer Names] ),
    VALUES ( Customer[Customer Names] ),
    BLANK ()
)

Thanks
Ankit J

5

Hi @Wlknsn,

@ankit is correct as far as I know there’s no way to do that…

  1. The table itself doesn’t get filtered because there are no matching values
  2. There is no “Object Model” we can reference to retrieve a random value entered in the Filter Pane
6

@Wlknsn,

Have you seen this? It doesn’t fully meet your requirement in terms of returning the filter context via measure, but does so via tooltip. I find it incredibly useful for unwinding complex filter context issues:

https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/

Hope this is helpful.

  • Brian
2 Likes
7

Hi @Brian
Nice Article as usual by SQLBI. However, if you look at the DAX code returned by Dump , it basically uses Filters function.

VAR ___f = FILTERS ( Customer[Address Line 1] )

I have already tested this function and it gives blank value for above scenario.

TestFilters = FILTERS(Customer[Customer Names])

Thanks
Ankit J

1 Like
8

@ankit,

Thanks very much for checking/testing that. I had responded on my iPad, so wasn’t able to open @Wlknsn’s PBIX file to examine his specific use case in detail. It’s still an excellent tool to keep in the toolbox, but unfortunately not useful here…

  • Brian
9

Hi @Wlknsn, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!