Forum Discussion
Slow when using filter pane
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)
4 Replies
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)?
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!Please provide sample data that properly demonstrates the relationship.
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.
