topic Slicers and Relationships in DAX Commands and Tips

Slicers and Relationships

Anonymous — Thu, 13 Feb 2025 19:31:55 GMT

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

Re: Slicers and Relationships

Anonymous — Thu, 13 Feb 2025 19:32:27 GMT

This is the chart that I referenced:

Re: Slicers and Relationships

Anonymous — Fri, 14 Feb 2025 02:08:31 GMT

Hi Anonymous ,

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

Re: Slicers and Relationships

Anonymous — Fri, 14 Feb 2025 22:11:01 GMT

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

Re: Slicers and Relationships

Anonymous — Mon, 17 Feb 2025 08:16:43 GMT

Hi Anonymous ,

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

Re: Slicers and Relationships

Anonymous — Tue, 18 Feb 2025 17:29:58 GMT

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:

Re: Slicers and Relationships

Anonymous — Mon, 31 Mar 2025 23:22:27 GMT

Anonymous any luck solving this issue? Thanks!