Forum Discussion
Relations & models
Hello Everyone
Wanted to ask you, i have a relation between sales & store (1 to many) & product to sales (1 to many)
my question, how to make it to display only products available in certain store, for example if i choose store 3, i want it to display only products of store 3, should i do a new table for that or is there some tip for that? thanks
8 Replies
chahineatallah you have few options to do that.
The wrong one will be to use bi directional filters.
The right one will be to create a help measure for that.
Here is a tutorial that explains how to do that:
https://www.sqlbi.com/tv/syncing-slicers-in-power-bi/Thanks will check it out
Hi,
I am not sure if I understood your question correctly, however, I tried to create a sample pbix file like the below.
One way to do is, insert a Sales total measure into the Filters pane and select the condtion = "is not blank".
Please check the below picture and the attached pbix file.
hi yes this what i want, but why in my model i have blank in store
so as summary , i have a model with store lookup, product lookup, and sales
i have also created a store parameter (just for training purposes), when i choose my parameter as store 3, i want it to show me only products in that store & its sales, this is link to my file, dont know why i couldnt attach
maven roasters pbcommunity.pbix
Hi,
Sorry that it is quite difficult for me to understand your data model.
Store Lookup table: only has store_id = 5
Sales by Store table: it has store_id = 3,5,8
I think, you cannot show store 3 and 8 by using store lookup table. It is correct that it only shows sotre 5, and rest of stores are shwon as blank, because only store 5 information exists.
Hi chahineatallah ,
I checked the PBIX file you shared and found that you selected the "store id" in the "Store Lookup" table, which only contains the number 5.
In “Food Inventory”, the “store id” contains 3.
So you can change the table and then you will get what you want: display only products of store 3.
This is the PBIX file, you can directly to view it for understanding.
Best regards,
Community Support Team Selina zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
thank you
hi again
in my model i have customer table which is related to sales table
also i have product table related to sales table
now when i create a matrix with customer ID & product, i get the products for each customers & sales for each customer by product, how is that? as customer & products are not related , to my knowledge it should not work
Thanks
link to my file
