topic Re: How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax in DAX Commands and Tips

How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax

JohnCarl45 — Wed, 26 Jun 2024 07:34:21 GMT

Hi folks, good day. I have a problem with my data set: how to convert 0.1 to 0.9 months into 0–1 years and 1.1 to 5 years into 1–5 years.
6.1 to 10 into 5–10 years, 11.1 to 15 into 10-15 years, and 15.1 to 20 into 15 years and above. Btw, here's the sample data. As you see, the column Years of Tenured is the one that I want to convert into text, like 0–1 years, etc., and the Years of Service column is the finished product when I convert those Years of Tenured values using Dax. The question is, what formula of dax will I use to convert those values in the Years of Tenured column to get the value in the Years of Service column? It's a big help if you answer this question. Thank you Very much

Years of Tenured Years of service
0.1 0-1 Years
0.2 0-1 Years
0.3 0-1 Years

1.0 0-1 Years
1.1. 0-1 Years
1.2 0-1 Years
1.3 0-1 Years
1.4 0-1 Years
2.0 1-5 Years
2.1    1-5 Years
2.2    1-5 Years
2.3    1-5 Years
3.0   1-5 Years
3.1    1-5 Years
3.2   1-5 Years
3.3   1-5 Years
4.0   1-5 Years
4.1   1-5 Years
4.2   1-5 Years
4.3   1-5 Years
5.0   1-5 Years
5.1   1-5 Years
5.2   1-5 Years
5.3 1-5 Years
6.0 5-10 Years
6.1   5-10 Years
6.2   5-10 Years
6.3   5-10 Years
7.0 5-10 Years
7.1   5-10 Years
7.2   5-10 Years
7.3   5-10 Years
8.0 5-10 Years
8.1   5-10 Years
8.2   5-10 Years
8.3   5-10 Years
9.0   5-10 Years
9.1   5-10 Years
9.2   5-10 Years
9.3   5-10 Years
10 5-10 Years
10.1   5-10 Years
10.2   5-10 Years
10.3   5-10 Years
11 10-15 Years
11.1 10-15 Years
11.2   10-15 Years
11.3   10-15 Years
12 10-15 Years
12.1   10-15 Years
12.2   10-15 Years
12.3   10-15 Years
13   10-15 Years
13.1 10-15 Years
13.2 10-15 Years
13.3 10-15 Years
14   10-15 Years
14.1 10-15 Years
14.2 10-15 Years
14.3 10-15 Years
15   10-15 Years
15.1 10-15 Years
15.2   10-15 Years
15.3   10-15 Years
16 15 Years Above
16.1 15 Years Above
16.2 15 Years Above
16.3 15 Years Above
17   15 Years Above
17.1 15 Years Above
17.2 15 Years Above
17.3 15 Years Above
18 15 Years Above
18.1   15 Years Above
18.2 15 Years Above
18.3 15 Years Above
19   15 Years Above
19.1 15 Years Above
19.2   15 Years Above
19.3 15 Years Above
20   15 Years Above

Re: How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax

freginier — Wed, 26 Jun 2024 14:12:01 GMT

Hello JohnCarl45

Do you try to do this using a new table in power bi linked to your data ? I think it easier than a DAX formula

Richard

Data SL Consulting

Re: How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax

Jihwan_Kim — Wed, 26 Jun 2024 14:14:23 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

It is for creating a new column.

And I suggest having a category table (disconnected table) like below.

Years of service CC = MAXX ( FILTER ( Category, Data[Years of Tenured] >= Category[Min] && Data[Years of Tenured] <= Category[Max] ), Category[Category] ) & " Years"

Re: How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax

Elcin_7 — Wed, 26 Jun 2024 14:17:55 GMT

You can create a conditional column in power Query.

you can adjust formula below depending your needs. Also formula might need some adjustment depending your years of Tenured colum is number or text. In this formula column data type is decimal number.

Tenure Category =

IF(

SELECTEDVALUE('table name'[Years of Tenured]) < 2,

"0-1 Years",

IF(

SELECTEDVALUE('table name'[Years of Tenured]) >= 2 && SELECTEDVALUE('table name'[Years of Tenured]) < 5,

"1-5 Years",

IF(

SELECTEDVALUE('table name'[Years of Tenured]) >= 6 && SELECTEDVALUE('table name'[Years of Tenured]) < 10,

"5-10 Years",

IF(

SELECTEDVALUE('table name'[Years of Tenured]) >= 10,

"10+ Years",

"Unknown"

)

Re: How to convert 0.1, 0.2, 0.3, 0.4, 0.5 months into 0-1 Years Using Dax

JohnCarl45 — Thu, 27 Jun 2024 04:37:16 GMT

Thank you so much sir it works!