Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |