Forum Discussion
Helper VWaterfall chart based on Two tables
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
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
5 Replies
- NimaiAhluwalia
Continued Contributor
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
Junaid11Hello 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
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