Return multiple variables

[Seanan]

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.

[SergioSilvaPT · Accepted solution]

@Seanan 

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.

[Seanan]

Hi @SergioSilvaPT 

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.

[SergioSilvaPT]

@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.

[Seanan]

Hi @SergioSilvaPT 

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.

[SergioSilvaPT]

Glad i could help!

Regards,

Sérgio Silva 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[SergioSilvaPT]

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 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[Seanan]

Hi @SergioSilvaPT 

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?