Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have one column which contains version of softwares like below
3.1.7.12
3.1.7.14
2.6.5
2.6.8
27.4.12.8
and so on...
My question is how can I format each numbers between two points to have every time 2 digits ? So:
03.01.07.12
03.01.07.14
02.06.05
02.06.08
27.04.12.08
Is it possible to do that with dax formulas ?
Sébastien
Solved! Go to Solution.
Hi @Hussard,
Based on my test, you could refer to below steps:
Sample data:
Create 5 columns:
B = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1))<2,"0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1),PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1)))
C = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2),PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2)))
D = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3),"."&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3)))
E = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4),"."&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4)))
New A = Table2[B]&Table2[C]&Table2[D]&Table2[E]
Result :
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Hussard
The following way you can get your solution easily.
I have given the Sample Pbix file link below. Hope this helps you.
Hi @Hussard
The following way you can get your solution easily.
I have given the Sample Pbix file link below. Hope this helps you.
Hi both,
Thanks a lot I tried both solutions and it suits me.
Regards
Sébastien
Hi @Hussard,
Based on my test, you could refer to below steps:
Sample data:
Create 5 columns:
B = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1))<2,"0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1),PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 1)))
C = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2),PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 2)))
D = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3),"."&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 3)))
E = IF(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4)=BLANK(),BLANK(),IF(LEN(PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4))<2,".0"&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4),"."&PATHITEM(SUBSTITUTE('Table2'[A],".", "|"), 4)))
New A = Table2[B]&Table2[C]&Table2[D]&Table2[E]
Result :
You could also download the pbix file to have a view.
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |