Creating a target line in a graph using two slicers as conditions

[Tim23]

Hello,

I am looking to add to a chart with statistical data per week, a target/goal line which has two main inputs:

1/ Project source (A, B or A&B)

2/ Origin (internal or external, or both)

In the lookup table I have defined the goals for each of the 4 combined sets of inputs (A & internal, A & external, B & internal, B & external). Depending on the selected slicers, it should create the sum of the goals to display the combined goal of the selected projects and origins.

now, I am facing two difficulties

1) The values of Project and Origin are defined from a large dataset, yet the target value is in a lookup table. I cannot actively create two relationships betewen the two table, so I managed to use measures to get it to work but either for project, OR for origin, which is not sufficient

2) with the above method, I am linking the target line to the existance of data points with the selected conditions, yet if in a CW there are no data for, let's say project A, it doesnt mean that the target is 0.

Thanks a lot in advance for your thoughts

[Anonymous]

Hi,

Thanks for the solution rohit1991  offered, and i want to offer some more information for user to refer to.

hello @Tim23 , Do you want to implement accumulation? If you want to achieve accumulation, if you have a date table, you can consider using datesytd() to achieve accumulation, you can refer to the following link about the function.

DATESYTD function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991]

Hi @Tim23 

This is a classic scenario in Power BI where slicers can break direct relationships, but it’s totally solvable!

The best way to keep your target/goal line always visible, no matter which slicer values are selected or if there’s missing data for certain weeks, is to:

1. Use disconnected slicer tables for Project and Origin. Create separate tables just for your slicers (not linked directly to your main data table). This lets users select any combination, even if it’s missing from the main table.

2. Always use a proper Date (Calendar) table on your X-axis. Link your data, your targets, and your visuals to this Date table. That way, your chart will display all weeks (or dates) whether or not there’s data in the main table.

3. Write a DAX measure to dynamically calculate the target. 

If you ever want your line to stay perfectly flat (showing the “goal” across all dates), you can create a scaffold table in Power Query with every possible combo of Project, Origin, and Date, then relate your target table to it.

[Tim23]

thanks, this is getting close, but not yet entirely where it shouold be: when in the data table there are no values for that CW, the target drops (however - if project A does not have any Customer origin values in CWx, then this does not mean the target value can be omitted, it should remain included. See below how the target line fluctuates based on data per CW. However, this line should be straight through the CW's.

[Tim23 · Accepted solution]

solved it by creating a separate Slicer Table, individually linked to Origin and Project tables as well as the big data table. This way, no matter the presence of the data in the Data table, the Slicer table will show and let the values be selected.