Forum Discussion
Advice on data and filtering
I have a table with several columns. One column named "Objective" contains various combinations of values from SO1 to SO6 (some have only one of these "SO" values, others have all 6). So I created 6 different columns to allow me to see which records have "SO1" in this column, which have "SO2", and so on up to "SO6". Those records which contain the "SO" values have a "Y" in the new columns.
What I'm trying to do is to filter visuals to show me only the records where there is a "Y" in the corresponding column based on a selection from "SO1" to "SO6" (e.g. selecting "SO2" from a dropdown will show only the records where "Y" is in the new "SO2" column).
I've attached an image of one of the visuals. Any advice on how to achieve this would be appreciated.
Thanks!
Hi willfrancis
One way or another, you should include in your model a table that relates Capabilities (assuming that's what each row of your current table represents) and Objectives.
Your current method of expanding the Objective column to six columns is not convenient for filtering.
The table relating Capabilities and Objectives (I'll call it Capability Objective) might look like this, with one row per combination:
Capability Objective can then be related to your original table using an appropriate key column (I invented Capability Key for illustration) with a bi-directional many-to-one relationship, and optionally to an Objective dimension table with a single directional many-to-one relationship.
Putting this together, the model might look like this:
You would then apply filters to Objective[Objective].
Note: You don't have to include the Objective table but I prefer to in case there are other Objective-related attribute columns that might be added.
Sample PBIX attached.
Would something like this work for you?
6 Replies
- OwenAuger
Super User
Hi willfrancis
One way or another, you should include in your model a table that relates Capabilities (assuming that's what each row of your current table represents) and Objectives.
Your current method of expanding the Objective column to six columns is not convenient for filtering.
The table relating Capabilities and Objectives (I'll call it Capability Objective) might look like this, with one row per combination:
Capability Objective can then be related to your original table using an appropriate key column (I invented Capability Key for illustration) with a bi-directional many-to-one relationship, and optionally to an Objective dimension table with a single directional many-to-one relationship.
Putting this together, the model might look like this:
You would then apply filters to Objective[Objective].
Note: You don't have to include the Objective table but I prefer to in case there are other Objective-related attribute columns that might be added.
Sample PBIX attached.
Would something like this work for you?
Thanks very much Owen, that works!
- Praful_Potphode
Hi willfrancis
try attached pbix.
i have used bridge table using power query to solve the issue.
Please give kudos or mark it as resolved once confirmed.
Regards,
Praful
Thanks very much Praful, I've tried your solution as it has worked too!
- Praful_Potphode
Hi willfrancis
Glad it worked for you.
You can mark multiple solutions as accepted.
If my solution also worked, request you to mark it as well
Regards,
praful
Hi Praful,
I have tried to mark your solution as accepted however the page is only allowing me to mark one solution as accepted at a time. I had marked Owen's solution as accepted as he had replied first. I'll try again in a short while to mark yours as accepted too.
Thanks!
