topic Ordering years from slicer in dax in DAX Commands and Tips

Ordering years from slicer in dax

Mike_1 — Fri, 30 Dec 2022 18:57:44 GMT

Hello,

Im struggling abit with the following

I got a slicer that collects years from the calender

so atm its showing 2017 - 2018 - 2019 - 2020 -2021 -2022

i used CONCATENATEX to make a var with all the years in it

then i used CONTAINSSTRING to check whatever is clicked and add data to that

the thing is now that i need some kinda loop and i dont know how to build it

there is a formula after that , thatgives weight to the years, depending on what is clicked in the slicer

so for example if clicked 2022 2021 2020 2018 --> 2022 = 50% , 2021 = 35% , 2020 = 10% , 2018 = 10%

but it should also do it when you skip a year or more years

so for example if clicked 2022 2020 2017 --> 2022 = 50% , 2020 = 35 % , 2017 = 15%

so basicly last year in selection allways have the biggest weight 50%

next one in row from the selected gets 35%

next one in row from the selected gets 15%

and so on

anyone abble to help me out here, my brain been explodnig to trying to figure this out and its probably so easy

thx Mike

Re: Ordering years from slicer in dax

tamerj1 — Fri, 30 Dec 2022 19:55:12 GMT

Hi Mike_1

many ways to do that but you need to be more specific about the weights. The latest selected year is 50% then 35% then 15% then 10% then? And how many years can be selected at once.
The first thing to do is not to use CONCATENATEX -with CONTAINSSTRING but to use VALUES or ALLSELECTED with CONTAINS instead.

Re: Ordering years from slicer in dax

Mike_1 — Fri, 30 Dec 2022 22:01:03 GMT

Thx for the reply 🙂

It can be anything, from minumum 1 year to maximum 5 years selected

i use a switch() to divide the % depending on the count of the selection

COUNTROWS(VALUES('Calendar'[Jaar])) outputs 1 2 3 4 or 5

the total will allways be 100% ( made a mistake in first post making it 105% ^^ )

so if only 1 is selected = 100%

2 selected , latest year 60% , other 40%

3 selected , latest year 50% , next in line 35%, next in line 15 %

4 selected , latest year 40% , next in line 30%, next 15%, next 5%
etc

but yeah like i said i need to tell the formula witch year needs what %

Re: Ordering years from slicer in dax

Mike_1 — Sat, 31 Dec 2022 07:41:23 GMT

Figured it out, got it working now

used RANKX to give them a prio, works like a charm 🙂

got rid of the CONCATENATEX and ONTAINSSTRING aswell, much better now, ty 🙂