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.
Hi all,
I feel this one should be a simple fix but its just not coming to me....
Ive tried LEFT / SWITCH functions but Ive yet to come up with the correct dax function.... I've seen this can be done very quickly within PowerQuery however the table I need to create the new column in was added as a UNION of different columns and my new table isn't showing within PowerQuery.
I would like to show the text column below within a new column as only numbers..... Please note Not Applicable will be equal to 0.
Score Text | Score Number |
10 Totally Satisfied | 10 |
5 Neutral | 5 |
1 Totally Dissatisfied | 1 |
Not Applicable | 0 |
Any help, as always, would be appreciated 🙂
THANK YOU!!
Solved! Go to Solution.
@eilidh3 does this work for you?
Column =
VAR _1 =
ADDCOLUMNS ( 'Table', "new", SUBSTITUTE ( 'Table'[Score Text], " ", "|" ) )
VAR _2 =
ADDCOLUMNS (
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
"_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
)
),
"num", IFERROR ( CONVERT ( [_txt], INTEGER ), 0 )
)
RETURN
CALCULATE (
MAXX (
ADDCOLUMNS (
'Table',
"scoreNum", MAXX ( FILTER ( _2, EARLIER ( 'Table'[Score Text] ) = [Score Text] ), [num] )
),
[scoreNum]
)
)
@eilidh3 does this work for you?
Column =
VAR _1 =
ADDCOLUMNS ( 'Table', "new", SUBSTITUTE ( 'Table'[Score Text], " ", "|" ) )
VAR _2 =
ADDCOLUMNS (
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
"_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
)
),
"num", IFERROR ( CONVERT ( [_txt], INTEGER ), 0 )
)
RETURN
CALCULATE (
MAXX (
ADDCOLUMNS (
'Table',
"scoreNum", MAXX ( FILTER ( _2, EARLIER ( 'Table'[Score Text] ) = [Score Text] ), [num] )
),
[scoreNum]
)
)
This works a charm! I would like to know how to do it the other way around, but can't seem to figure it out.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |