Forum Discussion
Return multiple variables
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.
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
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
7 Replies
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 & " " & _P4But i think this isn't your problem.
Regards,
Sérgio Silva
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.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?
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
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.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
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
