Slicer usage for two tables differently

[PrieleBI]

Hello,

Please help with this issue. How to created helper measure for this selection

I want to use only ID slicer and get this result for two tables:

1.Table 1 shows detailed information about ID;

2.Table 2 shows all the ID of the shop, where belongs selected ID (including selected ID).

Please check the picture attached.

[danextian · Accepted solution]

Hi @PrieleBI 

This design does not align with best practices for relationships. Typically, relationships should flow from Dim to Fact tables in a one-to-many structure. However, in your model, the relationships flow from Fact to Dim. Moreover, your sales table will likely be on the many side of relationships, so the flow should be toward it in a one-to-many structure, not the other way around. Additionally, the Shop Plan table is another fact table. To properly link the two fact tables, you'll need to create additional dimension tables, such as Shop and Product, to act as bridges.

Once the model is properly setup, you should be able to create working measures with a simple logic.

Sales Qty all ID = 
CALCULATE ( SUM ( Sales[SalesQuant] ), REMOVEFILTERS ( Sales[ID] ) )

Plan Execution = 
DIVIDE ( [Sales Qty all ID], SUM ( ShopPlan[Shop Plan] ) )

Please see the attached pbix.

[PrieleBI]

Thanks @danextian ,

One thing is missing till 100 proc. solution, could you helo with it.

Is it possible, that second table would show only Shop X  ? (because the selected ID is from Shop X, so only this Shop is needed in table data).

Logic: User choose ID -> Accoding to selection :

>> Table 1 shows detailed ID information

>> Tabel 2 shows the Shop information ( exactly that shop, where ID is from) and the shop data is from the same week as ID week. 

[danextian]

You can just visually filter it by Sales Qty all ID <> blank.

[Ashish_Mathur]

Hi,

Here's my take

1. Write a calculated column formula (LOOKUPVALUE() function) in the Sales table to bring over the Shop Plan column from the Shop Plan table.  The Shop Plan table will now be rendered useless for the purpose of calculations/building visuals.
2. Relationships with the Calendar table should always be w.r.t the Date column.  So both in the Calendar table and Sales table, create a proper date column.