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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jenny99
New Member

Many-to-Many relationship with multiplication between tables

I have a model with a many to many relationship.  In my example below egg is in two categories (Dairy and Protein).  I would like to incorporate the "Multiplier" column in the Bridge Table below to make eggs/protein a negative amount in the output, but have the eggs/dairy combination be positive.  All necessary relationships are set up in my data model.

 

Food

Egg
Bread
Milk

 

Category

Dairy
Protein
Grains

 

Food_Category_Bridge

FoodCategoryMultiplier
EggDairy1
EggProtein-1
BreadGrains1
MilkDairy1

 

Fact_Table

FoodSales
Egg5
Bread5
Milk5

 

Measures:

TotalSales = SUM(Fact_Table[Sales])

TotalSales_ManyToMany = CALCULATE([TotalSales],Food_Category_Bridge)

 

Desired Output:

FoodCategorySales
EggDairy5
EggProtein-5 (currently Positive)
BreadGrains5
MilkDairy5

 

My current output has a positive 5 on second row, I would like it to be negative by multiplying by the multiplier column in the bridge table above.  Any help on how to modify my existing measures to accomplish this is greatly appreciated.

 

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@jenny99 Assuming you have the following relationship

 

parry2k_0-1629850480242.png

 

Add this measure:

 

Sum Sales = 
CALCULATE ( 
    SUM ( 'Fact'[Sales] ),
    CROSSFILTER ( Food[Food], Food_Category[Food], Both ),
    CROSSFILTER ( Category[Category], Food_Category[Category], Both )
) 

* SELECTEDVALUE ( Food_Category[Multiplier] ) 

 

and here is the output, using the Food and Category column from the respective table (not from the bridge table)

 

parry2k_1-1629850538468.png

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
jenny99
New Member

Your solution worked very well for my simple example.  My actual model is a bit different.

 

My measure returns Blanks using: 

 

* SELECTEDVALUE ( Food_Category[Multiplier] )

 

 

It returns values (but wrong) if I use:

 

* SELECTEDVALUE ( Food_Category[Multiplier],1)

 

 

In my model, I have 4 Fact tables with a many to many relationship similar to the one shown.  All four bridge tables are related to a the Category_Description table as shown below.  In my reports, I am not showing the food, only category.  I add the four measures from each fact table to get the total and everything works fine.  The only problem is if I create a matrix from the Category_Description table below, the SelectedValue from your measure returns a blank.  If I create a Pivot table with the single measure from the fact table and drag the food and category tables to the row fields on a matrix, it works perfectly.  I just need to find a way to get the multiplication to work when using the table without the multiplication values.  I have tried RELATED and other things, but have been stuck.

 

Category_Description(Related to all 4 Bridge tables)

CategoryDescription
Grainsxyz Grains
Dairyxyz Dairy
parry2k
Super User
Super User

@jenny99 Assuming you have the following relationship

 

parry2k_0-1629850480242.png

 

Add this measure:

 

Sum Sales = 
CALCULATE ( 
    SUM ( 'Fact'[Sales] ),
    CROSSFILTER ( Food[Food], Food_Category[Food], Both ),
    CROSSFILTER ( Category[Category], Food_Category[Category], Both )
) 

* SELECTEDVALUE ( Food_Category[Multiplier] ) 

 

and here is the output, using the Food and Category column from the respective table (not from the bridge table)

 

parry2k_1-1629850538468.png

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors