topic Re: Relations & models in DAX Commands and Tips

Relations & models

chahineatallah — Sat, 23 Jul 2022 08:23:46 GMT

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

Re: Relations & models

SpartaBI — Sat, 23 Jul 2022 09:45:07 GMT

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/

Showcase Report – Contoso By SpartaBI

Re: Relations & models

Jihwan_Kim — Sat, 23 Jul 2022 09:53:08 GMT

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.

Re: Relations & models

chahineatallah — Sun, 24 Jul 2022 05:09:51 GMT

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

Re: Relations & models

chahineatallah — Sun, 24 Jul 2022 05:13:06 GMT

Thanks will check it out

Re: Relations & models

Jihwan_Kim — Sun, 24 Jul 2022 06:37:42 GMT

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.

Re: Relations & models

Anonymous — Tue, 26 Jul 2022 08:53:05 GMT

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

Re: Relations & models

chahineatallah — Wed, 27 Jul 2022 03:41:11 GMT

thank you

Re: Relations & models

chahineatallah — Sat, 30 Jul 2022 12:45:51 GMT

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

https://catallah-my.sharepoint.com/:u:/g/personal/chahineatallah_catallah_onmicrosoft_com/ESJ6nEkGn8ZAg0KFk76RFSMBXYaGByXeEAwwTdm9dICrtA?e=UC7gKJ