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.
I have list of roughly 126 values, for example P1 Voice1, P1 Voice 2 etc. I'm trying to create a function that will group these values based on a cut off point.
I have got the code for grouping them and creating the cut off point. However, I am struggling to return the variables. Would someone be able to help me with how to return multiple variables.
The code is as follows:
Split Assessment Type =
var _P1 = LEFT('AssessmentType_NameLocalizations'[Value],SEARCH("P1",'AssessmentType_NameLocalizations'[Value],1,LEN('AssessmentType_NameLocalizations'[Value]))+1)
var _P4 = LEFT('AssessmentType_NameLocalizations'[Value],SEARCH("P4",'AssessmentType_NameLocalizations'[Value],1,LEN('AssessmentType_NameLocalizations'[Value]))+1)
return
I'd like to return _P1 and _P4 and hopefully it will display the expected result.
The alternative would be to alter the SEARCH to contain "P1", "P4", "P7", "S3", however I'm not exactly sure how to SEARCH by mutliple conditions.
Solved! Go to Solution.
No problem, it's just because normally it's easier to help with the pbix file.
From what i understood you what someting like this:
If yes you will need to group the values and use that in the slicer:
If you don't know how to create those bins, you can do it easily in Power Query:
With 126 values it's a little time consuming but it can be done!
Regards,
Sérgio Silva
No problem, it's just because normally it's easier to help with the pbix file.
From what i understood you what someting like this:
If yes you will need to group the values and use that in the slicer:
If you don't know how to create those bins, you can do it easily in Power Query:
With 126 values it's a little time consuming but it can be done!
Regards,
Sérgio Silva
Below is 2 basic example I've quickly thrown together to help clarify.
The 2nd screenshot is the slicer. The left hand slicer (Column1) is how it looks now. The 2nd Slicer to the right Is what I want it to look like. So all 4 values will be grouped into 1 outcome "P1" but all of the data will be retained for all 4 voices.
Once "P1" is selected it the bar chart will display all of the data for all P1 values.
@Seanan I already understood, you can do it has i explained above, with the conditional column.
You can give a try to my solution above, but you can have an easier solution.
If the beginning is always the filter that you want to do, you can do it by adding a new column that, for example:
P1 Voice 1 -> you want P1
P1 Voice 2 -> you want P1
P2 Voice 1 -> you want P2
and so on...
In Power Query choose Add Column and select the column that you have the values P1 Voice 1, ...
Select Extract the First Characters:
and input 2, to return only P1, P2, P3 ...
And it will return a new column that you will use in the slicer...
Regards,
Sérgio Silva
Apologies I just saw your conditonal formatting column suggestion.
I've altered it a little so that the operator is "Contains" and this has given me the output I'm looking for.
Thank you once again for all your help.
Glad i could help!
Regards,
Sérgio Silva
Hi @Seanan ,
Do you have an pbi file with an example? So it's clearer for us to understand what you need to achieve...
To return the two variables that you created you can use the following:
Split Assessment Type =
var _P1 = LEFT('AssessmentType_NameLocalizations'[Value],SEARCH("P1",'AssessmentType_NameLocalizations'[Value],1,LEN('AssessmentType_NameLocalizations'[Value]))+1)
var _P4 = LEFT('AssessmentType_NameLocalizations'[Value],SEARCH("P4",'AssessmentType_NameLocalizations'[Value],1,LEN('AssessmentType_NameLocalizations'[Value]))+1)
return
_P1 & " " & _P4
But i think this isn't your problem.
Regards,
Sérgio Silva
Thanks for the response.
Unfortunately as the PBI file contains sensitive data I am unable to send it over, so I apologise for that.
I'll try to explain it a little better so that hopefully it is clear what I need to achieve.
I have a slicer that currently contains 126 options, for example P1 Voice 1, P1 Voice 2, P1 Voice 3 and P1 Voice 4. I'd like to group all of the P1, P4, P7 and S3 voices to just display P1, P4, P7 and S3 in the slicer (but still return all of the data for Voice 1 to Voice 4).
Does this help clarify a bit better?