Forum Discussion

Felipepbiplis's avatar
Felipepbiplis
Frequent Visitor
3 years ago
Solved

Filter Dax rows with list

how to filter rows from a table type 1 2 3 4  using a list column type 1,2,3,4  in another table using dax functions?   ex: select value 1,3 in list and show    1 3    in table rows   ...
  • puneetvijwani's avatar
    puneetvijwani
    3 years ago

    Felipepbiplis  As you mentioned there is no relation between table , A Nice way to solve this these using relationships with bridge table introduced between them and use that to get best out of the Power BI Modelling 

    However Alternately  you can create measure to pass one selected value from one visual to other and use that to apply filters on the other in Filters pane or play bit with visual interactions

    Here is DAX which can help you
    1. Selected Value = SELECTEDVALUE(TAble1[Profiles],"No filter")
    2.     If filtered =

    Var Filteredvalue =
    If(CONTAINSSTRING([Selected Value],SELECTEDVALUE(Table2[Ruler]) ),1,0)
    Return
    Filteredvalue
     

    Result will be something like below , Note that if i choose rick the IFfiltered measure becomes 1 for only those Rows in Table 2 , The you can use If filtered = 1 in your filter pane visual if you like 

    But I believe changing your model can you give much better and clean results !

    -------------------------------------------------------------------------------------------------------------------------------

     

    Good day, amigo! Have I provided the solution? If so, please let me know by adding the lovely, sweet "solution" tag to my post. And hey, if you're feeling very kind, give me a Kudos; after all, who doesn't enjoy a little digital gratitude?



puneetvijwani's avatar
puneetvijwani
Icon for Resolver IV rankResolver IV
3 years ago

Felipepbiplis  I tried to understand what you posted , Correct me if i am wrong


I am assuming you already expanded your original list in to rows 


if not then do expand it in Power Query  as below 

 

 

 

Then use the simple Filter using DAX to create a new Table 

 

Filtered table = FILTER( 'Table containing List',
                         'Table containing List'[Original List] = "1"
                           || 'Table containing List'[Original List] = "3")
 

Also replace "Table containing list" with the name of your original table , and replace column "Original List" with the name of that column.

Hope it Helps

  • Felipepbiplis's avatar
    Felipepbiplis
    Frequent Visitor
    3 years ago

    Okay, I'll try to explain better.

    There are two datatables in the power bi and
    the tables have no relationship and all data is text type, tables below:
    table1

    NamesProfiles
    AdrianABC
    RickABC,XYZ
    JhonABC,XYZ,UVZ
    MikeABC,XYZ


    table2

    RulerValor1Valor2
    ABC1a3c
    XYZ2b3b
    UVZ1a2d

     

    I'm going to place the two tables in a common table visual on the dashboard.
    When I click on a name in the table 1 visual, I want table2 to filter and show the filtered profiles rule corresponding to the name that was clicked.

    example:

    If I click on Rick in table1

    table2 will look like this

    RulerValor1Valor2
    ABC1a3c
    XYZ2b3b


    thanks!