How to assign zero to any value of a column at a table when choosing a filter from other table

[Anonymous]

HI ,

I have 3 tables that are connected through date relationship .

 cretaed measures using values from both column to calculate the metric using date filter common at all three connected .

I want to add a extra filter ( 10 choices )  that only exist at one of the tables and I want anytime I select this filter assign value zero for any metrics comming from the third table at calculation .

No filter : Table1[A]+Table2[B] = A+B

with Filter : Table1[A}+Table2[B]=A+0 =A

How Can I do that usind extra Auxilary table and manage relationship between ?

Thank you in advance 

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

Thank you for your reply I've created this sample and used relationship to connect these tables

I have a main date table that other tables are connected through the date from the main table .

we have a date slicer and a  filter as long is "tenure" filter is ALL commute 1 + commute 2 = overall commute 

as soon as select any value from Tenure Commute 2 regardless of any value will be 0 and Commute 1 = overall commute 

I need to create a bridge table whenever filter frm sheet 1 applied only collect data from sheet one table Imagine In real I have 11 diffrent table so looking for a bridge table .

[v-yalanwu-msft]

Hi, @Anonymous ；

You could create a measure as follow:

commute total = IF(ISFILTERED(sheet1[Tenure]),SUM('sheet2'[comute]),SUM('sheet2'[comute])+SUM(sheet1[commute per year]))

however i don't understand commute 1 + commute 2 ? where is it? I tested by other value. 

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.