Dynamic filter on a table, then EXCEPT on it

[LogiqueNeutre]

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

CapturedFilter = SELECTEDVALUE('AnotherTableWithUniqueValuesOfFilter1'[Filter1])

to then create a calculated intermediary table

IntermediaryTable =
VAR SelectedFilter = [CapturedFilter]
RETURN
CALCULATETABLE (
VALUES ( 'Table1'[Number1] ),
FILTER (
'Table1',
'Table1'[Filter1] = CapturedFilter &&
'Table1'[Filter1] <> "" &&
TRIM('Table1'[Filter1]) <> ""
)
)

then the final result table would be something like :

FinalTable =
EXCEPT(
SELECTCOLUMNS(Table2, "Filter1", Table2[Filter1]),
SELECTCOLUMNS(IntermediaryTable, "Filter1", IntermediaryTable[Filter1])
)

but my IntermediaryTable is empty and i can't find why...

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

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.

[LogiqueNeutre]

I'm afraid it simply cannot be done, because

1) a slicer value cannot be used in a calculated table,

2) when i use a measure, which is supposed, according to other sources, to be able to retrieve the slicer value, it doesn't work... but maybe i do it wrong?..

[dufoq3]

Hi @LogiqueNeutre,

1. you send your request to PowerQuery forum but you are probably asking for DAX solution
2. 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

[LogiqueNeutre]

Hello,

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 | Filter1
5239 | APAOR
8558 | APCA
4536 | DIS

3 rows of Table 2 :
Number1
5239
4536
8426

Expected results :
if my Filter1 is set on APAOR, i expect the IntermediaryTable to show
Number1
5239

and the EXCEPT to show
Number1
4536
8426

Thanks for your help.

[dufoq3]

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

You you can do that directly in Power Query:

Table2 with added [Fitlter1] column

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

[LogiqueNeutre]

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 | Date
5239 | APAOR | 01.02.2017
5239 | APCA | 01.05.2017
8558 | APCA | 01.05.2017
4536 | DIS | 01.03.2017
1234 | TER | 30.11.2017
1234 | TER | 12.12.2017
1234 | APCA | 01.03.2018

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

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.

Can a filter be captured in a variable in a calculated column or a measure ?

Thank you for your help, it is much appreciated.

[dufoq3]

Provide new sample data for both tables and expected result based on sample data please. It would be better to explain with examples.

[LogiqueNeutre]

Here is the Power BI file : https://file.io/PQft2g3eKpdM

The added data compared to the screenshot below is to see my tries at doing the IntermediaryTable and the FinalTable.

edit : oh, i forgot in the power bi file to do the 1->several relationship between Table2 and Table1. Seems it was done automatically though.

[dufoq3]

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.