Forum Discussion
Select value and show corresponding values associated within group of selected value in bar chart?
- 4 years ago
user35131 I highly recommend using Dimension tables, so your model should look like this:
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
https://excelwithallison.blogspot.com/search?q=it%27s+complicated
The screenshot above I'm suggesting for your model is a little unorthodox, but so is what you're asking for.
To make this model, you will need to create a DimTable (or grab it direct from data source if possible). I created the one in the attached in Power Query: Right click on your one table > Reference. Select only the Num, Group (and any other columns directly related to this dimension). You can use the Ctrl key to select multiple columns. Then Right click on one of the selected columns > Remove other Columns. Finally, ensure there are no duplicates on the lowest level of granularity (in the example data you provided that's Num). See my links above for Unique key descriptions if needed.
Then, as smpa01 has suggested, we do a self-join, but unlike smpa01 's example, my solution only requires doing the self join on the Dimension table. To do this, Duplicate the DimNum table in Power Query (Right click on the DimNum table > Reference). Rename it DimNum_Associated.
This method avoids any duplication of the Fact table, so keeps your model more efficient and data model size smaller.
Result is:
But if you use the DimNum filter, you'll only get records for that num = 8:
Let me know if anything doesn't make sense or doesn't work, and provide screenshots/detailed error messages.
user35131 to be able to achieve this, you can create a derived table where you perform a self-join with the table itself to come to a calculated table. Once you have that, you can create a relationship between source and derived table and it will give you what you need. Pbix is attached.