Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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
Super User
Super User

@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
Not applicable

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 .

 

KT1598_0-1648233053301.png

KT1598_0-1648234522590.png

 

KT1598_2-1648233431889.pngKT1598_3-1648233461869.png

 

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:

vyalanwumsft_0-1648523151331.png

vyalanwumsft_1-1648523156825.png


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.