Forum Discussion
Dynamic filter on a table, then EXCEPT on it
Community ChampionIf 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
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.
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.