Forum Discussion
Slicers from complex data model
I have a table more or less like:
| Project Name | Fiscal Year | Office Assigned | Project Type |
| A | 2020 | Hiring, Finance | L |
| B | 2021 | Finance | T |
| C | 2021 | Finance, Advertising | T |
| D | 2020 | Advertising, Hiring | L |
I have a visual showing counts of project type by year, and it's useful (and functioning). But I need to show the number of project types done by each office. If I do a slicer by Office Assigned, I wind up getting each combination of offices as options, instead of just single offices.
So in my data model I've duplicated my main table, and then split the office column by delimiter into separate rows so that I have just individual offices available (I need to do it in a separate table so that my overall count of items doesn't increase). But when I create a slicer on the main visual using the office list generated from the table with the split values, chosing a value doesn't change the visuals. It looks like the association between the two tables is correct (flowing from original to the split).
Ideas?
Hi amanda_wahlig,
It seems to me like you've taken the right approach by splitting the Office Assigned column into separate rows in a separate table to avoid increasing the count of items in the original table.
When creating a slicer on the main visual using the office list generated from the table with the split values, you must make sure that the relationship between the two tables is set up correctly. You'll need to create a relationship between the Office Assigned column in the original table and the corresponding column in the new table containing the split values.
Once you've set up the relationship, try selecting a value from the slicer to see if the visuals update accordingly. If the visuals still don't update, check that the slicer is connected to the correct table and column.
Another option to consider is to use a filter instead of a slicer to display the number of project types done by each office. You could add a visual-level filter to the main visual and choose the Office Assigned column from the new table containing the split values. Then, select the specific office(s) you want to include in the filter.
I hope these suggestions help! Let me know if you have any further questions.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly
2 Replies
Hi amanda_wahlig,
It seems to me like you've taken the right approach by splitting the Office Assigned column into separate rows in a separate table to avoid increasing the count of items in the original table.
When creating a slicer on the main visual using the office list generated from the table with the split values, you must make sure that the relationship between the two tables is set up correctly. You'll need to create a relationship between the Office Assigned column in the original table and the corresponding column in the new table containing the split values.
Once you've set up the relationship, try selecting a value from the slicer to see if the visuals update accordingly. If the visuals still don't update, check that the slicer is connected to the correct table and column.
Another option to consider is to use a filter instead of a slicer to display the number of project types done by each office. You could add a visual-level filter to the main visual and choose the Office Assigned column from the new table containing the split values. Then, select the specific office(s) you want to include in the filter.
I hope these suggestions help! Let me know if you have any further questions.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quicklyIt still wasn't quite working, but the upshot of the final easy step was that the relationship between the original table and the table with the split values had to be bi-directional.
