topic Re: Year Diff with Slicer in DAX Commands and Tips

Year Diff with Slicer

KW123 — Wed, 09 Nov 2022 20:20:15 GMT

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?

Re: Year Diff with Slicer

tackytechtom — Wed, 09 Nov 2022 20:35:45 GMT

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 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Year Diff with Slicer

KW123 — Wed, 09 Nov 2022 21:26:24 GMT

tackytechtom

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

Re: Year Diff with Slicer

tackytechtom — Thu, 10 Nov 2022 20:15:52 GMT

KW123 ,

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? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Year Diff with Slicer

KW123 — Thu, 10 Nov 2022 22:16:58 GMT

tackytechtom

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

Re: Year Diff with Slicer

tackytechtom — Sat, 12 Nov 2022 10:07:14 GMT

Hi ,

How about this:

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"
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

KW123

Re: Year Diff with Slicer

KW123 — Mon, 14 Nov 2022 18:10:19 GMT

tackytechtom

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