Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamic filter on a table, then EXCEPT on it

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 : ...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Anonymous,

  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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      dufoq3
      Community Champion
      2 years ago

      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's avatar
      dufoq3
      Community Champion
      2 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.