Slicers and Relationships

[brunakopik]

Hello,

I am trying to get the filters to update one of the charts I have in my dashboard:

 Updating Media

This is what I would like to do (not working right now):

When I click on Select All, it shows all 5 colours

When I click on 2023/24, show 2020, 2022, target 2024/25

When I click on 2024/25, show 2020, 2022, target 2024/25, 2024

When I click on 2025/26, show 2020, 2022, target 2024/25, target 2025/26

Here is the data:

Fiscal Year data (slicer):

Chart data:

What type of column should I add to both datasets so that I can build a relationship? I can't simply do fiscal years because I have SelectAll in the slicer.

Thanks!

Bruna

[brunakopik]

@Anonymous  any luck solving this issue? Thanks!

[Anonymous]

Hi  @brunakopik ,

Is your sample data in the following image style with a [Select All] column next to it to create a hierarchical effect:：

You can change measure to the following:

Flag =
var _select = SELECTCOLUMNS('Fiscal Year data',"select",[Fiscal Year])
var _count=COUNTX(ALLSELECTED('Fiscal Year data'),[Fiscal Year])
return
IF(
    HASONEVALUE('Fiscal Year data'[Select ALL])=TRUE()&&_count=3,1,
SWITCH(
    TRUE(),
_count=1&&CONTAINSSTRING("2023/24",_select)=TRUE()&& MAX('Table'[Year]) in {"2020","2022","Target 2024/25"} ,1,
_count=1&&CONTAINSSTRING(_select, "2024/25")=TRUE() && MAX('Table'[Year]) in {"2020","2022","Target 2024/25","2024"},1,
_count=1&&CONTAINSSTRING(_select, "2025/26")=TRUE() && MAX('Table'[Year]) in {"2020","2022","Target 2024/25","Target 2025/26"},1,0
))

Best Regards,

Liu Yang

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

[brunakopik]

Thanks, but it is not working:

It onlt shows 2020 data for 2023/24 and SelectAll:

For 2024/25:

2025/26:

Here are is the DAX:

And filters:

[Anonymous]

Hi  @brunakopik ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Fiscal Year data'[Fiscal Year])
RETURN
IF(
    HASONEFILTER('Fiscal Year data'[Fiscal Year])=FALSE(),1,
SWITCH(
    TRUE(),
_select = "2023/24" && MAX('Table'[Year]) in {"2020","2022","Target 2024/25"},1,
_select = "2024/25" && MAX('Table'[Year]) in {"2020","2022","Target 2024/25","2024"},1,
_select = "2025/26" && MAX('Table'[Year]) in {"2020","2022","Target 2024/25","Target 2025/26"},1,0)  )

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Slicer – Select all:

Slicer – 2023/24:

Best Regards,

Liu Yang

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

[brunakopik]

Hi,

Thank you!

It didn't work - I wonder if it's because the slicer is single select? Here is what the data looks like:

And in the report:

Slicer:

What can I do instead?

Thanks,

Bruna

[brunakopik]

This is the chart that I referenced: