Forum Discussion
Issue with Relation and Slicer
Hey guys!
Need a help in setting properly a relation between two tables.
First one:
| Date | Organization | Cat1Count | Cat2Count |
| 4/12/2022 | Text | Decimal | Decimal |
Second one:
I needed to build a summary table presenitng count % share of those categories that's why I used PowerQuery GroupBy function to get Cat1Count and Cat2Count grouped by Date and Org and then I unpivoted it to have table looking as below:
| Date | Organization | Category | Count |
| 4/12/2022 | Text | Text | Decimal |
All seem to be fine. However I need to create some viz'es based on first table and some based on the unpivoted one, those two are supposed to share Date and Org slicers. And here's my issue - when relation is set between them on Date field, the Date slicer works perfectly with content based on both tables I mentioned however Organization slicer works only with data from same table. When reverse (setting relation on Org) Organization slicer works well but Date slicer works only with vizes based on same table. I tried various relationships, applying cross filter and security filter and nothing. Would appreciate any suggestions! Thanks!
Hi Anonymous ,
You need to create two dimension tables and associate the date column and organization column, like this:
Organization = VALUES('Table'[Organization])Dates = CALENDAR( MIN('Table'[Date]), MAX('Table'[Date]) )Then use the dimension tables for filtering.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , first of you should have explored opportunity not to group the data.
As you want treat both of them as Facts. You need to have a common Organization and date dimension and join with both table
Separate out Dimension Table(Power Query)- https://youtu.be/vHuhbvYCiNc
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos. Thanks for quick reply! Tried to create table without prior grouping and unfortunately didn't work. Same issue - slicer works only when relation is set on field which was used to build it. Also, I can't separate proper dimension table since I don't have any KeyValue field to set proper relation then.
I guess it's abput Many to Many relation I need to set between these tables, no matter if that's Date or Org column.
v-kkf-msftCommunity Support
Hi Anonymous ,
You need to create two dimension tables and associate the date column and organization column, like this:
Organization = VALUES('Table'[Organization])Dates = CALENDAR( MIN('Table'[Date]), MAX('Table'[Date]) )Then use the dimension tables for filtering.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-kkf-msft That's great! Thank you! It's working now! 🙂
