Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

 

 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous Assuming you have the following relationship

     

     

    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)

     

     

    ✨ 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.⚡

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous Assuming you have the following relationship

     

     

    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)

     

     

    ✨ 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.⚡

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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