Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
PrieleBI
Frequent Visitor

Slicer usage for two tables differently

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.

 

 

PrieleBI_0-1737824759889.png

 

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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.

 

danextian_1-1737867053762.png

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] ) )

 

danextian_2-1737867129246.png

Please see the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

4 REPLIES 4
danextian
Super User
Super User

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.

 

danextian_1-1737867053762.png

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] ) )

 

danextian_2-1737867129246.png

Please see the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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. 

PrieleBI_0-1737883872552.png

 

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










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Ashish_Mathur
Super User
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.