Visual Filtering

[Anonymous]

Hi All,

I currently have two tables as per the below dataset examples;

Task ID	Tonnage
1	1
2	2
3	4
4	2
5	2
6	4

1st table

Task ID	Height
1	2
2	1
3	2

2nd table

Please note the 1st table with 'Tonnage' included has more 'Task ID' fields than the second table. I want to display a card visual showing the total SUM tonnage but only show the SUM tonnage for all Task ID's in the 2nd table. Please note both real sets of data are over 100,000 rows long so it is not practical and manually filter the 1st tables 'Task ID'.

Many thanks,

E

[sdjensen]

Create a new table with the distinct values of task_id from 2nd table and link the 1 tables to the new table. Then create a filter in the report where you deselect values on blank task_id (first select all and then deselect blank - this will create a different from filter that will include new task_id when models update). This should only include task_id present in the 2nd table.

[amitchandak · Accepted solution]

@Anonymous , Try like

calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in Values(Table2[Task ID])))
calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in allselected(Table2[Task ID])))

Or use treatas

https://docs.microsoft.com/en-us/dax/treatas-function

[Pragati11]

Hi @Anonymous ,

Have you got any relationship between these 2 tables?

Thanks,

Pragati