Building a dashboard with smartsheets data

[Anonymous]

I'm trying to create a dashboard with data pulled from smartsheets. There I have a table with columns "Task Name" and Category"

When loaded into PBI the table looks like this:

It appears PBI created the Sheet Location column for the smartsheets expand/collapse feature, but I'm having issues with being able to filter data associated to a given project.

I want to create a dashboard where users can select items from the Task Name with Category - 'Project' to show the associated milestones. For instance, if I select Marketing Initiatives from Task Name I want the report to display Subscription Model Implementation and Budgetary Items under Review.

[amitchandak]

@Anonymous 

If you create a slicer from the task, is it not allowing you to filter?

Also refer :https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

 if you need more help make me @

Appreciate your Kudos.

[kentyler · Accepted solution]

Very interesting.

So it looks like a "project name" could be found by filtering the the sheet location column for entries that don't have a slash

that gives you a list for your slicer

you could then filter for the milestones by filtering the sheet location column for entries that contained the project name

or it might be easier to create a calculated column that pulled the project name out of those rows

and then use a relationship to hook your slicer up to that column

that would involve using a "reference" query that would pull out only the project name values, which would mean it would appear in the data model and let you set up the reference.

[Anonymous]

This worked perfectly, thank you!