Forum Discussion
Create Slicer that does a 'like' match on table
I have this data of Facilities. And they want to filter on Facility Type. However, the way the data is stored in the DB it's simply values selected separated by semicolons. So, this is the unique values that are in the DB:
So, I want to create a slicer that gives the unique values.
So, I created a custom table like this, it has a friendly name and the search name.
How can I associate this to the other table by pulling up all the values with the "search on" column. I can't create a relationship between the two. What options do I have to be able to search with this?
Hi Thomas_MedOne,
Solution 1:
- Create a new column in this new table with concatenation of Facility Type and Search On. It will bascially be facilitytypesvalue column from your 1st snapshot.- Join your original table (facilitytypesvalue) to this new table (new column similar to facilitytypesvalue).
- In the slicer, pull Facility Type and enable search (see below) for the slicer.
Solution 2:- Instead of creating a new table, create new columns in the same table with Facility Type and Search On.
- In the slicer, pull Facility Type and enable search for the slicer.
Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!
Proud To Be a Super User !!!
LinkedInHi Thomas_MedOne, I'm just guessing by the description you gave us, that you have a table of facilities, that has a column with facility type values? Maybe something like in the example below, which I created in Power Query.
If this is the case, what you could do in Power Query is create new query by referencing the Facilities query. Then the following steps:
- Remove all columns except the FacilityId and FacilityTypeValues column.
- Split the FacilityTypeValues column by the semicolon delimiter
- Select the FacilityId column and unpivot the other columns
- Remove the attribute column
- Then you see the result below, where you see the FacilityId which each type it has assigned
Then you can create a mapping table and merge it with this table.
Now create a relationship between this table and the Facilities table and you use a slicer with the FacilityType field to easily select the facility type and filter the facilites. Check the attached sample pbix.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
5 Replies
Hi Thomas_MedOne, I'm just guessing by the description you gave us, that you have a table of facilities, that has a column with facility type values? Maybe something like in the example below, which I created in Power Query.
If this is the case, what you could do in Power Query is create new query by referencing the Facilities query. Then the following steps:
- Remove all columns except the FacilityId and FacilityTypeValues column.
- Split the FacilityTypeValues column by the semicolon delimiter
- Select the FacilityId column and unpivot the other columns
- Remove the attribute column
- Then you see the result below, where you see the FacilityId which each type it has assigned
Then you can create a mapping table and merge it with this table.
Now create a relationship between this table and the Facilities table and you use a slicer with the FacilityType field to easily select the facility type and filter the facilites. Check the attached sample pbix.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
This was super helpful. I learned a lot.
Hi Thomas_MedOne,
Solution 1:
- Create a new column in this new table with concatenation of Facility Type and Search On. It will bascially be facilitytypesvalue column from your 1st snapshot.- Join your original table (facilitytypesvalue) to this new table (new column similar to facilitytypesvalue).
- In the slicer, pull Facility Type and enable search (see below) for the slicer.
Solution 2:- Instead of creating a new table, create new columns in the same table with Facility Type and Search On.
- In the slicer, pull Facility Type and enable search for the slicer.
Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!
Proud To Be a Super User !!!
LinkedInHi,
In Power Query, split the content of that column into rows based on the ; delimiter. Now create a Dim table from this column.
Pleas try options suggested by community users.
if it doesnt work please share sample input/output.
Thanks and Regards,
Praful
