filtering related tables

[dw700d]

I have established a 1 to many relationship between two tables . The “Location Table” contains unique identifiers and the “Transaction Table” contains many.The tables are below.

                        Location Table

Asset #	Address	Employee	Cost
1	1st street	Mike	100
2	19th avenue	Mike	200
3	brown rd	Dave	100
4	7th avenue	Dave	400
5	tulip lane	Ron	500
6	8th street	Ron	600

              Transaction Table

Reference	Asset #	Exemption
A	1	Y
B	2	Y
D	4	N
D	3	N

I have created a new table on my dashboard and I have added the "Exemption column" from the "Transactions Table" to columns from the "Locations Table". My problem occurs when I try to filter the new table  by the Exemption column. All columns filter except the cost column. For instance, when i filter the new table by Exemption = "Y" the total cost should be 300 but that is not occuring the total cost remains 1900.

The other columns that are responding to the filter are text columns. Why wont my cost column( which is numerical) respond?

[v-shex-msft]

HI @dw700d,

Did you mean to create a dynamic calculated column based on the filter effect? If that is the case, it is impossible.
Calculate column and filter are hosted on different levels and you can't use the child to affect the parent.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng