Forum Discussion

theo's avatar
theo
Helper III
4 years ago

filter table based on dynamic table

Hi, Im looking for a way to filter table (TargetTable) based on the data (FilterTable) that I can modify.  So when I update outside of Power BI and then refresh it in PowerBi then the TargetTable is also updated. 

 

Currently, I am using summarizedcolumns to created the TargetTable

 

TargetTable = SUMMARIZECOLUMNS(MainTable[index],filter(MainTable,MainTable[GroupSum]=6&&

MainTable[group 1]>=1 && MainTable[group 1]<=3 &&
MainTable[group 2]>=0 && MainTable[group 2]<=1 &&
MainTable[group 3]<=2))

 

dynamic FilterTable should look like this

 

GroupSumGroup 1Group 2Group 3
6100
 211
 3 2

 

which I am thinking I could create this instead but both does not work properly when referred in the formula.

GroupSumMin_Group 1Min_Group 2Min_Group 3Max_Group 1Max_Group 2Max_Group 3
6000312

 

TargetTable = SUMMARIZECOLUMNS(MainTable[index],filter(MainTable,MainTable[GroupSum]=related(filterTable[GroupSum] ))

 

Will appreciate any suggestion.

Creating a measure is not an option though since when used, I have memory error (I have about 40 similar filters).

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You cannot have dynamic tables outside of measures.  Do you mean "as dynamic as the dataset refresh" ?

     

    By the way you can simplify your formula

    TargetTable = SUMMARIZECOLUMNS(MainTable[index],
filter(MainTable,MainTable[GroupSum]=6&&
MainTable[group 1] IN {1,2,3} &&
MainTable[group 2] IN {0,1} &&
MainTable[group 3] IN {0,1,2}))
    • theo's avatar
      theo
      Helper III
      4 years ago

      lbendlin thanks for the suggested simplification.

      Yes, the numbers to be in the filter are based on the supposed table that will be updated every now and then.  It will also be loaded, so everytime I udpate it, the resulting formula I expected to be updated as well.

      I was able to use the relatedtable, but it only provide correct filter for the column that is directly has the relationship.  Is there any solution outside of measure?  Can you also share the possible measure solution, and will try it. thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi theo ，

     

    When the data source is refreshed, the calculated table will also be refreshed, that is, a dynamic table.

     

    From your description, you want to create a pivot table.

    Here's a link about pivot table using dax, hope to help you:

    https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • theo's avatar
      theo
      Helper III
      4 years ago

      Anonymous  Actually my problem is getting the right formula to refer to a table which be updated every now and then.  I tried using relatedtable, but it only provide correct filter for the column that is directly has the relationship.

      I dont see the pivoting will resolve it.