Add slicer option as dynamic filter to line graph

[DataOK]

Hello,

I want to have a line graph based on a slicer selection.

My data looks like this: (this is already a table created as measure for some other reports)

Servicename	Availability	Date	Department
Service 1	100%	01.01.2025	Group A
Service 2	100%	01.01.2025	Group B
Service 3	100%	01.01.2025	Group C
Service 4	99,99%	01.01.2025	Group B
Service 5	98,99%	01.01.2025	Group A
Service 1	98,99%	01.02.2025	Group A
Service 2	99,99%	01.02.2025	Group B
Service 3	95,99%	01.02.2025	Group C
Service 4	100%	01.02.2025	Group B
Service 5	100%	01.02.2025	Group A
Service 1	97,99%	01.03.2025	Group A
Service 2	96,99%	01.03.2025	Group B
Service 3	98,99%	01.03.2025	Group C
Service 4	99,55%	01.03.2025	Group B
Service 5	97,77%	01.03.2025	Group A

The User selects one of the Groups (A, B or C) via slicer and on the page there are a couple of line graphs that should display the corresponding services.  For Group A in this case Service 1 and 5 in two different line charts. In the line chart there should be shown the data for every month of the current year and the last year. (this is done via the filter option for the visual). But with this filter option (in advanced filter) I can only filter for Servicename and add two options e.g. one for Group A and Group B but not for Group C as third option.

I also tried to create a measure for this and add this as filter to the line chart (or table to check the values). It shows the correct values within a table (with option do not summarize) but did not work for line chart.

Graph3 = 
IF ( 
    "Group A" in VALUES(Dyn_Table_Availability[Department]), 
    CALCULATE(DISTINCT(Dyn_Table_Availability[Availability]), Dyn_Table_Availability[Servicename]="Service 1"), 
    IF (
          "Group B" in VALUES(Dyn_Table_Availability[Department]),
          CALCULATE(DISTINCT(Dyn_Table_Availability[Availability]), Dyn_Table_Availability[Servicename]="Service 2"),
          IF (
            "Group C" in VALUES(Dyn_Table_Availability[Department]),
            CALCULATE(DISTINCT(Dyn_Table_Availability[Availability]), Dyn_Table_Availability[Servicename]="Service 3"),
            BLANK())
        )
    )

Any idea? Thx for any help. 

[burakkaragoz]

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.

[DataOK]

Hey @burakkaragoz ,

thanks for the quick response. 

Yes sure your solutions looks much easier to maintein for the future. 

It also looks fine for me. But it do not show the correct figures! It seems that the average is not the original number! Example result from my date with your dax above. From my point of view this is due to the used AVERAGE command.

Month	Availability	shown average with your dax
January	100,00	99,99
February	99,99	99,99
March	100,00	99,99
April	100,00	99,01
May	99,78	99,70
June	99,97	97,62

[burakkaragoz]

@DataOK ,

Ah, you're absolutely right! AVERAGE is messing up your numbers because it's averaging across multiple rows when there might be multiple records per month.

The problem: If you have multiple services or multiple records for the same month, AVERAGE calculates the mean of all those values, not the actual availability figure you want.

Try this instead:

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

Or if you know there should only be one value per month:

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

Better approach - check your data structure first: Can you confirm how many rows you have per month for each service? If there are multiple rows, that explains why AVERAGE is giving you different numbers.

Alternative if you have multiple records per month:

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

This takes the last value for each month instead of averaging.

What does your raw data look like - one row per service per month, or multiple rows?

[DataOK]

Unfortunately I could not manage to get it working.

I have added a demo file with some sample data and charts. But I´m not allowed to upload a file here. 

So I added again my sample data which I use for this. 

Servicename	Availability	DATE	Department
Service 1	100	01.01.2025	Group 1
Service 1	90	01.02.2025	Group 1
Service 1	80	01.03.2025	Group 1
Service 1	70	01.04.2025	Group 1
Service 1	60	01.05.2025	Group 1
Service 1	50	01.06.2025	Group 1
Service 2	95	01.01.2025	Group 2
Service 2	85	01.02.2025	Group 2
Service 2	75	01.03.2025	Group 2
Service 2	65	01.04.2025	Group 2
Service 2	55	01.05.2025	Group 2
Service 2	45	01.06.2025	Group 2
Service 3	99	01.01.2025	Group 3
Service 3	88	01.02.2025	Group 3
Service 3	77	01.03.2025	Group 3
Service 3	66	01.04.2025	Group 3
Service 3	55	01.05.2025	Group 3
Service 3	44	01.06.2025	Group 3
Service 4	40	01.01.2025	Group 1
Service 4	50	01.02.2025	Group 1
Service 4	60	01.03.2025	Group 1
Service 4	70	01.04.2025	Group 1
Service 4	80	01.05.2025	Group 1
Service 4	90	01.06.2025	Group 1
Service 5	70	01.01.2025	Group 2
Service 5	60	01.02.2025	Group 2
Service 5	50	01.03.2025	Group 2
Service 5	40	01.04.2025	Group 2
Service 5	30	01.05.2025	Group 2
Service 5	20	01.06.2025	Group 2
Service 6	66	01.01.2025	Group 3
Service 6	77	01.02.2025	Group 3
Service 6	88	01.03.2025	Group 3
Service 6	99	01.04.2025	Group 3
Service 6	100	01.05.2025	Group 3
Service 6	100	01.06.2025	Group 3
Service 7	100	01.01.2025	Group 1
Service 7	100	01.02.2025	Group 1
Service 7	100	01.03.2025	Group 1
Service 7	80	01.04.2025	Group 1
Service 7	80	01.05.2025	Group 1
Service 7	80	01.06.2025	Group 1

-----
edit -  what I have seen is that it seems the filter is for the service is not working as excpeted as all services are shown:

[v-saisrao-msft · Accepted solution]

Hi @DataOK,

Please refer the attached pbix file for your issue:

Hope this helps

Thank you.

[DataOK]

Hey @v-saisrao-msft 

thanks for the pbix. That really looks good for my solution. I slightly changed a bit the line graph and it works fine for me.

1 & 2.  I changed the "Servicename" filter to the one from Table "ServiceDepartmentMap" so that the used filter for the group is working (it than shows only the services that are relevant for this group and only 1 value (instead of 24)). Also I selected the service that is valid for this specific line graph. In the chart (in 6) the other service for group a is shown (I also added this to the titel so that it is automaticly switched witht the group). 

3. Group filter is than valid selection for the line graphs and they are switched by selecting the groups

4. I changed the values for x / y axle and legend to have the time graph for every year in the line graph

5. in the line graph now the correct single value is displayed for every single data point (no more average over many service / mixing up the values)

6. service name is also dynamic based on selection per line graph. 

Perfect solution! Thank you all very much for your help!