Dynamic filter on a table, then EXCEPT on it

Question

Hello the Power BI community,after a lot of work, i'm stuck. I tried several AI help in order not to bother humans, but they circle their answers eventually.Here is what i'm trying to achieve :i have a Table1 with a column Number1, a column Filter1, and other columns,i have a Table2 with a column Number1, and other columns,i must find a way to get this result :after setting a filter on Filter1 on a visual, i need a table FinalTable having all lines containing each number of Number1 in Table2 that is NOT in Table1 when the value of Filter1 is the filter. (I went for just the column of Number1 first, i can always add later the other columns.)So naturally i went for an EXCEPT, but it seems Power BI cannot calculate things based on a visual. So the last way is, apparently, to use calculated columns and measures after capturing the value of the filter with&nbsp;CapturedFilter = SELECTEDVALUE('AnotherTableWithUniqueValuesOfFilter1'[Filter1])&nbsp;to then create a calculated intermediary table&nbsp;IntermediaryTable =VAR SelectedFilter = [CapturedFilter]RETURNCALCULATETABLE (VALUES ( 'Table1'[Number1] ),FILTER ('Table1','Table1'[Filter1] = CapturedFilter &amp;&amp;'Table1'[Filter1] &lt;&gt; "" &amp;&amp;TRIM('Table1'[Filter1]) &lt;&gt; ""))&nbsp;then the final result table would be something like :&nbsp;FinalTable =EXCEPT(SELECTCOLUMNS(Table2, "Filter1", Table2[Filter1]),SELECTCOLUMNS(IntermediaryTable, "Filter1", IntermediaryTable[Filter1]))&nbsp;but my IntermediaryTable is empty and i can't find why...&nbsp;edit: should i add, of course, that i manually checked many of the 11000 rows of Table1 and Table2, that there is a link between them on Number1, yes that link works since i use them all the time on 8 other pages filled with working visuals, yes it's the same Text format and Text type of data in Number1 (yes, text, and it's normal, sorry for calling it Number1 here)... The "&lt;&gt; "" " and "TRIM" are to make sure to remove all empty and blank entries of Number1. I did many checks of the intermediary values, changes to the Filter, but nope, can't find out where the problem is.&nbsp;You may correct this or find me an entirely new solution, i'll be happy anyway.Sorry if i made any mistake writing all this, i admit i'm really tired. I'll correct if i or you find any.Thanks a lot for any help.&nbsp;

dufoq3 · Answer

Hi&nbsp;Anonymous,

you send your request to PowerQuery forum but you are probably asking for DAX solution
it would be better if you provide sample data in usable format (read note below if you don't know how to do it) and expected result based on sample data

anonymous · Answer

Hello,&nbsp;you're probably right. Should i try to remove this post and recreate it in DAX solutions' category ?Here are a few examples of data :3 rows of Table 1 :Number1 | Filter15239 | APAOR8558 | APCA4536 | DIS&nbsp;3 rows of Table 2 :Number1523945368426Expected results :if my Filter1 is set on APAOR, i expect the IntermediaryTable to showNumber15239&nbsp;and the EXCEPT to showNumber145368426&nbsp;&nbsp;Thanks for your help.

dufoq3 · Answer

If you have distinct values in Table1 [Number1] column, create One to Many relationship between Number columns and you can start using filter as described.

dufoq3 · Answer

If you don't have distinct values in Table1 [Number1] column, you can create calculated column in Table2 and lookup for [Filter1] column from Table1 (see attached pbix file).
&nbsp;
You you can do that directly in Power Query:
&nbsp;
Table2 with added [Fitlter1] column

&nbsp;
let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUytlTSUVJwDHD0D1KK1YlWsjA1tYCIODuCBUxMjc1AAi6ewUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Filter1 = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUytlSK1YlWMjE1NgMzLEyMgIxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t]),
    MergedQueries = Table.NestedJoin(Table2, {"Number1"}, Table1, {"Number1"}, "Table1", JoinKind.LeftOuter),
    // Table2 with Filter1 from Table1
    ExpandedTable1 = Table.ExpandTableColumn(MergedQueries, "Table1", {"Filter1"}, {"Filter1"})
in
    ExpandedTable1
&nbsp;

anonymous · Answer

Ok, here is more information.In Table 1, the data has often several values of Filter1 for each Number1, sometimes one, sometimes none. Below, an example of another column (to show we don't have exactly the same entries each time).Number1 | Filter1 | Date5239 | APAOR | 01.02.20175239 | APCA | 01.05.20178558 | APCA | 01.05.20174536 | DIS | 01.03.20171234 | TER | 30.11.20171234 | TER | 12.12.20171234 | APCA | 01.03.2018Table2 has only 1 value of each Number1.So, doing what you suggested in Table2 would stop the relationship 1 to several between Number1 of Table2 to Number1 of Table1.&nbsp;Also, a question : does a calculated column recalculate itself each time a filter is changed ?So, would a solution close to the one you suggested actually reupdate the table each time the filter is changed ? And so, would the final table reupdate itself based on the intermediary table each time the filter is changed ?(ideally, once a solution is found, i will have many filters manually selected by the user and constantly changing the results of the final table)In what you have written, is my final table "MergedQueries" ? I'm quite new to Power BI, so, i don't exactly understand how Power Query can be put into visualisations and used by the report's final users.&nbsp;Can a filter be captured in a variable in a calculated column or a measure ?&nbsp;Thank you for your help, it is much appreciated.

Forum Discussion

Dynamic filter on a table, then EXCEPT on it

8 Replies

Related content

Dynamic Filter

Dynamic filtering between two tables

Dynamic Filtering

Filtered table

Dynamic measure filtering