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'm working on an dashboard to help me complete my timesheets based on categories I assign my calender items (meetings) + the time I spend on this calender item. I assign on average 3-4 tags/categories to a calender item. And I've created a naming convention to indicate the different contexts of a tag. (context 1 start with _; Context 2 start with .; Context 3 start with @; Context 4 start with a number)
The list of categories in powerbi - data view is seperated using ; . And the different contexts are not sorted. (they depend on the sequence when selecting the categories in outlook).
I'm looking for a DAX statement (I'm unable to install R script) that will extract the _Context 1 value.
And the closed I came in DAX is
Which finds me the context 1 value, but does not limit the extract to context 1.
Could someone write me the right DAX statement or point me in the right direction ?
Please find below some sample data and the expected value of the DAX statement
Input Expected DAX result
_AAAAA;1.000m - Next action;@COMPUTER;.Prj - 11111 _AAAAA
_BBBB;@AGENDA _BBBB
_AAAAA;.Prj - 11111 _AAAAA
.Prj - 11111;_AAAAA;1.000m - Next action;@COMPUTER _AAAAA
_AAAAA;1.000m - Next action;@AGENDA;.Prj - 11111;@CALLS _AAAAA
_CCCCCC;@COMPUTER _CCCCCC
_EEEEE;@AGENDA _EEEEE
_DDDDD;.Prj - 222;.Prj - 33333 _DDDDD
_DDDDD;.Prj - 222 _DDDDD
_BBBB;@AGENDA _BBBB
_BBBB;@AGENDA _BBBB
@COMPUTER;_BBBB _BBBB
.Prj - 33333;_DDDDD _DDDDD
_AAAAA;.Prj - 11111 _AAAAA
_EEEEE;.Prj - 222 _EEEEE
_AAAAA;.Prj - 11111 _AAAAA
_EEEEE;.Prj - 222 _EEEEE
@COMPUTER;_EEEEE _EEEEE
@COMPUTER;_DDDDD _DDDDD
_AAAAA;.Prj - 11111 _AAAAA
.33333;_BBBB _BBBB
_FFFFF _FFFFF
Many thanks for your support.
Solved! Go to Solution.
@Anonymous-
Hopefully someone comes up with something prettier 🙂
Final Solution = VAR __NumChars = SEARCH ( ";", TableName[Input], 1, 0 ) - SEARCH ( "_", TableName[Input], 1, 0 ) VAR __Positive = IF ( SIGN ( __NumChars ) > 0, TRUE (), FALSE () ) VAR __NotPositive = IF ( NOT ( __Positive ), IFERROR ( SEARCH ( "_", TableName[Input], SEARCH ( ";", TableName[Input], 1, 0 ), -1 ), -1 ) ) RETURN IF ( __Positive, LEFT ( TableName[Input], __NumChars ), IF ( IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) > 0, MID ( TableName[Input], SEARCH ( "_", TableName[Input], 1, 0 ), IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) - SEARCH ( "_", TableName[Input], 1, 0 ) ), IF ( IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) = 0, RIGHT ( TableName[Input], LEN ( TableName[Input] ) - IFERROR ( SEARCH ( "_", TableName[Input], 1, 0 ), -1 ) + 1 ), TableName[Input] ) ) )
Proud to be a Super User!
@Anonymous-
Hopefully someone comes up with something prettier 🙂
Final Solution = VAR __NumChars = SEARCH ( ";", TableName[Input], 1, 0 ) - SEARCH ( "_", TableName[Input], 1, 0 ) VAR __Positive = IF ( SIGN ( __NumChars ) > 0, TRUE (), FALSE () ) VAR __NotPositive = IF ( NOT ( __Positive ), IFERROR ( SEARCH ( "_", TableName[Input], SEARCH ( ";", TableName[Input], 1, 0 ), -1 ), -1 ) ) RETURN IF ( __Positive, LEFT ( TableName[Input], __NumChars ), IF ( IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) > 0, MID ( TableName[Input], SEARCH ( "_", TableName[Input], 1, 0 ), IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) - SEARCH ( "_", TableName[Input], 1, 0 ) ), IF ( IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) = 0, RIGHT ( TableName[Input], LEN ( TableName[Input] ) - IFERROR ( SEARCH ( "_", TableName[Input], 1, 0 ), -1 ) + 1 ), TableName[Input] ) ) )
Proud to be a Super User!
This did the trick. I did had to change the ',' by ';' but this is probably related to my local language setting.
Many thanks to this active forum & your support 🙂
Hi @Anonymous
you can get something very close by using "Split Column" when you import your data in the query editor.
split by _ then by ; then delete the columns you don't need.
Thx for the insight.
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |