Slow when using filter pane

[Anonymous]

Hi, I have 2 main tables in my model.

I need to show which ID's QTY is not match and the variance so that I have a measure:

variance = sum(table1[QTY]) - sum(table2[QTY])

I want to show the items only when Variance <> 0. Then I put this measure in the filter pane with Variance is not blank.

However, I found the filter make this table loading being very very slow (~30 seconds), when no filter (5 seconds)

May I ask is there anyway to fix it or is it possible to filter the table by the measure 'variance'? I may really need some helps here.

table1:

ID	QTY
1	10
2	10
3	10

table2:

ID	Country	QTY
1	A	10
2	A	5
3	B	10

Desired Result:

ID	Country	Variance
2	A	5

Doing it in PowerQuery is not an option because user want to filter it by other columns (e.g. Country)

[Ashish_Mathur]

Hi,

You can merge Table1 into Table2 using the Query Editor.  Then write a calculated column formula to calculate the difference between the quantity columns.  Build your visual from the consolidaed Table2.  Use this measure

Variance = sum(Data{Difference[)

In the filter pane, apply a condition of Variance <> 0.

Hope this helps.

[lbendlin]

Should we assume that Table1 and Table2 are linked via the ID ?  Which of them is the  parent table?  or is it a 1:1 relationship (in which case the tables can be merged)?

[Anonymous]

@lbendlin , yes, the key column is "ID" to map.

It is a One to Manay relationship between Table1 : Table2

Cannot do merge in this case because Table2's QTY can be filtered by other columns before calculating the variance so it is needed to be on the DAX.
Thanks!

[lbendlin]

Please provide sample data that properly demonstrates the relationship.