filter table based on dynamic table

[theo]

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

GroupSum	Group 1	Group 2	Group 3
6	1	0	0
	2	1	1
	3		2

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

GroupSum	Min_Group 1	Min_Group 2	Min_Group 3	Max_Group 1	Max_Group 2	Max_Group 3
6	0	0	0	3	1	2

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

[v-stephen-msft]

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]

@v-stephen-msft  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.

[lbendlin]

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]

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