Add slicer option as dynamic filter to line graph

Hi DataOK ,

Your DAX is way too complicated for what you're trying to do. You're fighting against Power BI instead of letting it work naturally.

The real issue: You're using DISTINCT() which returns a table, but line charts need single values. That's why it works in a table but not in your line chart.

Easiest fix - ditch the complex measure: Just put Department in a slicer, then create your line charts with:

• X-axis: Date
• Values: Availability
• Legend: Servicename

When someone picks "Group A", they'll automatically see Service 1 and Service 5 lines. No fancy DAX needed.

If you really want separate charts for each service:

Service 1 Chart = 
CALCULATE(
    AVERAGE(Dyn_Table_Availability[Availability]),
    Dyn_Table_Availability[Servicename] = "Service 1"
)

Make one measure per service, then separate line charts.

Your nested IF approach is a nightmare to maintain. What happens when you add Group D? More nested IFs?

Better dynamic approach:

Dynamic Service = 
VAR SelectedGroup = SELECTEDVALUE(Dyn_Table_Availability[Department])
VAR ServiceName = 
    SWITCH(SelectedGroup,
        "Group A", "Service 1",
        "Group B", "Service 2", 
        "Group C", "Service 3"
    )
RETURN
CALCULATE(
    AVERAGE(Dyn_Table_Availability[Availability]),
    Dyn_Table_Availability[Servicename] = ServiceName
)

Much cleaner and you can easily add more groups later.

The slicer + natural filtering is probably what you actually want though.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.