Multiselect dropdowns stored in database

[beakon]

Hi! I did some research in the community but wasn't able to find a solution to my problem. Our web applications stores the values selected in dropdowns as values in the database (rather than the actual label), so for example a dropdown that may look like this in the front end:

Will look like the following in the database:

• team_member
• contractor_employee
• contractor_user

In this case we have been using if statements to replace these (it does mean that every time we change a dropdown we also need to udpate the statement), but when we have multiselect dropdowns like the following:

In the database they will be separated by commas and stored in the same column, like the following:

• Administrator, team_member,branch_manager

This means that when adding them to a pie chart or a filter rather than displaying the individual values they all come up at the same time.

For example if a record is both an administrator and a team member, we would like that to appear in both these "slices" of the pie chart, rather than creating a new with with these two options. For the time being we have been creating an extra table just to store this information and we have been treating it as a relationship in Power BI. Is there a better way to achieve this?

[Icey · Accepted solution]

Hi @beakon ,

Is this what you want?

1. Enter data to create a Role table, with no relationship among other tables.

2. Create a Measure.

Measure = 
VAR SelectedRole =
    SELECTEDVALUE ( Role[Role] )
VAR ThisRoles =
    MAX ( 'Table'[Role] )
VAR Inside =
    SEARCH ( SelectedRole, ThisRoles,, 0 )
RETURN
    IF ( Inside <> 0, 1 )

3. Create other Measures filtered with [Measure].

User Name = 
CALCULATE (
    CONCATENATEX ( 'Table', 'Table'[Name], ", " ),
    FILTER ( 'Table', [Measure] = 1 )
)

Number of User = CALCULATE ( COUNTROWS( 'Table' ), FILTER ( 'Table', [Measure] = 1 ) )

Number of Role = CALCULATE ( COUNTROWS( 'Role' ), FILTER ( Role, [Measure] = 1 ) )

4. Create visuals.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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