cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Year Diff with Slicer

Hi,

I have a column that shows the year difference between opening and account and closing the account.  I'd like to make a slicer which has the following options

SLICER:
<1 year
1-3 years
3-5 years
>5 Years

Is there a way to create a slicer that could show this?

1 ACCEPTED SOLUTION
Super User

Hi ,

```SWITCH (
TRUE,
'Query1'[Year Diff] < 1,"<1",
'Query1'[Year Diff] >= 1 && 'Query1'[Year Diff] <= 3,"1-3",
'Query1'[Year Diff] >= 4 && 'Query1'[Year Diff] <= 5,"4-5",
">5"
)```

@KW123

 Did I answer your question❓➡️ Please, mark my post as a solution ✔️

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

6 REPLIES 6
Super User

Hi @KW123 ,

I'd suggest you add a new column with the logic above to create the bins you need.

Then use that column in your slicer.

Try to create the column as upstream as possible and as downstream as necessary, meaning add the column in the source if possible. If that does not work do it rather in Power Query as in DAX.

Let me know if this helps 🙂

 Did I answer your question❓➡️ Please, mark my post as a solution ✔️

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Helper V

@tackytechtom

Thank you.  I am not sure how to do that.

Super User

Create a new column with an if statement where you implement your logic like

if table[YearDifference] < 1 then "<1 year" else

if table[YearDifference] >= 1 and table[YearDifference] < 3 then "1-3 years" else

if table[YearDifference] >= 3 and table[YearDifference] < 5 then "3-5 years" else

">5 Years "

Note, the code above is just a pseudo code. You have to google the right syntax yourself depending on whether you are using DAX code or Power Query's M.

Does this help? 🙂

 Did I answer your question❓➡️ Please, mark my post as a solution ✔️

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Helper V
if ('Query1'[Year Diff] <1,"<1",
if ('Query1'[Year Diff] = 1 || 2 || 3, "1-3",
if ('Query1'[Year Diff] = 4 || 5 ,"4-5",
if ('Query1'[Year Diff] >5, ">5"))))
I have that but I am not sure where to put the ELSE.  It seems as though the DAX works for <1 and 1-3 but it is not producing the 4-5 or >5

Super User

Hi ,

```SWITCH (
TRUE,
'Query1'[Year Diff] < 1,"<1",
'Query1'[Year Diff] >= 1 && 'Query1'[Year Diff] <= 3,"1-3",
'Query1'[Year Diff] >= 4 && 'Query1'[Year Diff] <= 5,"4-5",
">5"
)```

@KW123

 Did I answer your question❓➡️ Please, mark my post as a solution ✔️

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Helper V

@tackytechtom

Thank you so much, this is exactly what I am looking for!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors