cancel
Showing results for 
Search instead for 
Did you mean: 

Many-to-Many Relationship or Bridge Table - Any Measure Does Not Sum Properly

Hi,

I have tried different modeling techniques, first I tried a bridge table between historical orders (received pivot) and future orders, and I used bi-directional filters, which resulted in incorrect quantities exploded in the visual.  Next, I tried a many-to-many relationship between the historical orders and future orders table, and still the measures I'm trying to calculate off the future orders table do not calculate properly. For example, my future orders table shows 830,000 items on future orders Oct-Dec, but if I sum the future order quantity I get over 66 million per month...which is absolutely incorrect.   

Will someone please help?  

Future Orders quantity for October are correctly loaded into Power BI Desktop, and should total 830,000.

JaclynPugh2022_6-1665005955729.png

Measure: 

FTR QTY = SUMX(VALUES('FUTURE ORDERS'[Product Number]),CALCULATE(SUM('FUTURE ORDERS'[Future Quantity])))
 
Result using data Model: Many-to-Many Relationship between received pivot (historical orders) and Future Orders.
Visual - Results in Exploded Quantity:
JaclynPugh2022_2-1665005096290.png

Example with Product Number Bridge table:

JaclynPugh2022_4-1665005391822.png

 

Visual Results in Exploded and incorrect quantity with Bridge Table:

JaclynPugh2022_5-1665005769784.png

Please help!  I must figure out what I am doing wrong it seems to be tied to the data model.

 

Thank you


 

Status: Investigating

Hi @JaclynPugh2022 ,

 

Is the product number in your Matrix in your bridge table, if yes, please check if this below meets your needs.

FTR QTY =
CALCULATE (
    SUM ( 'FUTURE ORDERS'[Future Quantity] ),
    FILTER ( 'FUTURE ORDERS', 'FUTURE ORDERS'[Product Number] )
        = SELECTEDVALUE ( 'All Products Union'[Product Number] )
)

 

Best Regards,
Community Support Team _ Caitlyn

Comments
v-caitlyn-mstf
Community Support
Status changed to: Investigating

Hi @JaclynPugh2022 ,

 

Is the product number in your Matrix in your bridge table, if yes, please check if this below meets your needs.

FTR QTY =
CALCULATE (
    SUM ( 'FUTURE ORDERS'[Future Quantity] ),
    FILTER ( 'FUTURE ORDERS', 'FUTURE ORDERS'[Product Number] )
        = SELECTEDVALUE ( 'All Products Union'[Product Number] )
)

 

Best Regards,
Community Support Team _ Caitlyn

JaclynPugh2022
Helper II

Hi Catilyn,

 

I tested your recommendation and recieved this error, " FILTER has been used in a True/False expression that is used as a table filter expression. This is not allowed"

JaclynPugh2022_0-1665058752218.png

 

JaclynPugh2022
Helper II

@Community Support Team _ Caitlyn

 

Can you or somone please help me out from here?  Any other ideas?  I need to complete this for my employer so your help is really important.

 

Thank you