Waterfall chart based on Two tables

[Junaid11]

Hello,

I have two tables and I want to have waterfall chart based on division by ID of both tables. Both the table share similar ID. Below are my tables:

Date	ID	Volume
17/01/2022	AB-1	345356
11/01/2022	AB-2	89643
18/01/2022	AB-1	23574
25/01/2022	AB-2	6379
11/01/2022	AB-3	35855

Date	ID	Volume
11/01/2022	AB-1	4567
25/01/2022	AB-2	89643
11/01/2022	AB-1	445
25/01/2022	AB-2	233
17/01/2022	AB-1	7676

I have connected them with new date table as I have few other tables so all are conected with same date table. Everything is working fine. I have used Simple Waterfall chart visual from App source. I have summed above table Volume and named it as Planned and for second table I named its volume as Planned.

I want waterfall to look like this below picture:

But problem is when I put ID column from first table it shows only that ID data along with random planned as it is from other table similarly If I put ID from second table then it shows correct planned value but wrong actual value and actual value division as zero.

I have attached the picture as well as pix file if anybody can help whether it is possible or not.

https://drive.google.com/file/d/1OEaPw191NP81sLddmz0GE2UTbrHStLMn/view?usp=sharing

Thank you

[NimaiAhluwalia]

Hello @Junaid11 

The problem you are facing is due to the relationship issue as there is no connection in the model between the ID of those 2 tables as the connection is made only from the date table.

I would not recommend the M*M as to eliminate that you can create a separate table with unique ids from both the tables.

IF MY SOLUTION HELPED YOU PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT SOLVES YOUR ERROR!

Regards,

Nimai Ahluwalia

[Junaid11]

Hello @NimaiAhluwalia ,

Yes I know that but I already have realtionship with date table to which I have make relationship of all the tables. We cannot have active relationship one for date and one for ID column I guess. Is there any other solution that we can keep Date relationship among both the table as well as get the ID based waterfall chart?

[NimaiAhluwalia · Accepted solution]

Yes, you have 2 options either you can follow the below link and use the DAX USERELATIONSHIP and use an in-active relationship.

https://www.sqlbi.com/articles/using-userelationship-in-dax/

Or create a separate table with unique ids from both the tables as i mentioned previously.

For the optimization use the Filtering direction to a single side instead of both sides.

IF MY SOLUTION HELPED YOU PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT SOLVES YOUR ERROR!

Regards,

Nimai Ahluwalia

[Junaid11]

Hello @NimaiAhluwalia ,

I have managed to add a Bridge Table but its value is repeating in water fall chart once for planned and once for actual

[NimaiAhluwalia]

you can use 

Table = DISTINCT(UNION(VALUES(Sheet1[ID]),VALUES(Sheet2[ID])))