Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I am struggling with this simple problem.
My data set:
name | amount |
a | 500 |
b | 500 |
c,d | 500 |
e,f,g | 600 |
h | 1000 |
I,j,k,l | 1000 |
The output I want:
name | amount |
a | 500 |
b | 500 |
c | 250 |
d | 250 |
e | 200 |
f | 200 |
g | 200 |
h | 1000 |
i | 250 |
j | 250 |
k | 250 |
l | 250 |
Here simple,
I am splitting the amount by the number of values I can find in the name column.
I can only use DAX as my dataset is a Calculated Table. Please help.
Solved! Go to Solution.
Hi @Devesh ,
My error, did not notice the last part that you asked for the split check the formula and PBIX revised:
Word List =
VAR SplitByCharacter = ","
VAR Table0 =
ADDCOLUMNS (
GENERATE (
Unpivot,
VAR TokenCount =
PATHLENGTH ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ) )
RETURN
GENERATESERIES ( 1, TokenCount )
),
"NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] ),
"Numberofwords",
LEN ( Unpivot[name] ) - LEN ( SUBSTITUTE ( Unpivot[name], ",", "" ) ) + 1
)
RETURN
SELECTCOLUMNS( Table0, "AMount", Divide(Unpivot[amount], [Numberofwords]), "Name", [NameSplit])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Devesh ,
Using this post you can do the following calculation:
Word List =
VAR SplitByCharacter = ","
VAR Table0 =
ADDCOLUMNS (
GENERATE (
Unpivot,
VAR TokenCount =
PATHLENGTH ( SUBSTITUTE (Unpivot[name], SplitByCharacter, "|" ) )
RETURN
GENERATESERIES ( 1, TokenCount )
),
"NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] )
)
RETURN
SELECTCOLUMNS( Table0, "AMount", Unpivot[amount], "Name", [NameSplit])
PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
The amount does not get divided using the formula you posted. It just copies the amount into all CSV values.
Hi @Devesh ,
My error, did not notice the last part that you asked for the split check the formula and PBIX revised:
Word List =
VAR SplitByCharacter = ","
VAR Table0 =
ADDCOLUMNS (
GENERATE (
Unpivot,
VAR TokenCount =
PATHLENGTH ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ) )
RETURN
GENERATESERIES ( 1, TokenCount )
),
"NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] ),
"Numberofwords",
LEN ( Unpivot[name] ) - LEN ( SUBSTITUTE ( Unpivot[name], ",", "" ) ) + 1
)
RETURN
SELECTCOLUMNS( Table0, "AMount", Divide(Unpivot[amount], [Numberofwords]), "Name", [NameSplit])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |