Forum Discussion
Helper IIImake a slicer visualisation which affect to other tables
Need Help please......,
I have 4 tables : Transaksi (transaction), Target, Gudang (Ware house / Stock) and Area.
I want to make a visuallisation which has 3 slicer : cabang (brancH) , komoditas (commodity) and area.
This slicer must be affect to 3 visual table Transaksi (transaction), Target and Gudang (Ware house / Stock).
Here is the file
https://drive.google.com/file/d/1sMee4wsg-MfjfQTiFHaawrFINs2gKAdV/view?usp=drive_link
Thankyou for helping me
Hi Ajeck ,
Looking at your data model, I would probably structure it a bit differently:
I added a new table using the distinct values from Komoditas field of the Target table (assuming Target table would contain at least one row for each Komoditas) as follows (Modeling>New table):
Komoditas = DISTINCT(Target[Komoditas])
Then I hooked up the slicers as:
Area slicer: area[Area]
Cabang slicer: area[Cabang]
Komoditas slicer: Komoditas[Komoditas]
I think this makes a cleaner schema structure. But do verify if it meets your requirements (Area and Cabang slicer will filter each other by design).
Hope it helps!Hi Ajeck ,
generally you should avoid many to many (*:*) relationships to prevent ambiguity and other pitfalls. There are many videos and articles out there explaining this topic in detail.
I will try to give you at least a few specific pointers based on my understanding of your model:
- You current model and report does the following
- The "komoditas" slicer is based on the "komoditas" columns of table "Target" but the relationsship to "transaki" is based on "key". That means you don't filter "komoditas", you filter combinations of "cabang" and "Komoditas" according to the definition of key
- It's just the same with "cabang". Additionally setting a filter to cabang in target table will not distribute this filter properly to your whole model.
- What I would recommend
- Create proper dimensions tables for canbang and komoditas with unique values
- If the relationsship between area and canbang is 1:1, vou don't necessarily need an extra cabang table
- If you create an extra cabang table define a relationship to the area table. That should be the only relationsship of your area table
- Remove all relationships between your fact tables (transaki, target, gudang)
- Define 1:* relationsships from your dimension tables to your fact tables (transaki, target, gudang)
- Assign only columns from your dimension tables to your slicers
- Create proper dimensions tables for canbang and komoditas with unique values
Hope that helps!
- You current model and report does the following
5 Replies
v-hashadapuCommunity Support
Hi Ajeck , Thank you for reaching out to the Microsoft Community Forum.
We find the answers shared by alish_b and Hans-Georg_Puls are appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you Hans-Georg_Puls and alish_b for your valuable response.
- alish_b
Super User
Hi Ajeck ,
Looking at your data model, I would probably structure it a bit differently:
I added a new table using the distinct values from Komoditas field of the Target table (assuming Target table would contain at least one row for each Komoditas) as follows (Modeling>New table):
Komoditas = DISTINCT(Target[Komoditas])
Then I hooked up the slicers as:
Area slicer: area[Area]
Cabang slicer: area[Cabang]
Komoditas slicer: Komoditas[Komoditas]
I think this makes a cleaner schema structure. But do verify if it meets your requirements (Area and Cabang slicer will filter each other by design).
Hope it helps! 
Hans-Georg_PulsHi Ajeck ,
generally you should avoid many to many (*:*) relationships to prevent ambiguity and other pitfalls. There are many videos and articles out there explaining this topic in detail.
I will try to give you at least a few specific pointers based on my understanding of your model:
- You current model and report does the following
- The "komoditas" slicer is based on the "komoditas" columns of table "Target" but the relationsship to "transaki" is based on "key". That means you don't filter "komoditas", you filter combinations of "cabang" and "Komoditas" according to the definition of key
- It's just the same with "cabang". Additionally setting a filter to cabang in target table will not distribute this filter properly to your whole model.
- What I would recommend
- Create proper dimensions tables for canbang and komoditas with unique values
- If the relationsship between area and canbang is 1:1, vou don't necessarily need an extra cabang table
- If you create an extra cabang table define a relationship to the area table. That should be the only relationsship of your area table
- Remove all relationships between your fact tables (transaki, target, gudang)
- Define 1:* relationsships from your dimension tables to your fact tables (transaki, target, gudang)
- Assign only columns from your dimension tables to your slicers
- Create proper dimensions tables for canbang and komoditas with unique values
Hope that helps!
- You current model and report does the following
- v-hashadapu
Hi Ajeck , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
