Subtract two columns with Many to Many Relationship - Likely Measure

[USABB_Data]

I realize there are other questions with the same title but there are no solutions that fit my scenario.

Tables:

1. Sales Detail table with millions of rows and repeat Item #'s obviously (on different orders)

2. Discount table with different groups of items group by a Discount Name.  Each Discount Name has unique items but items will repeat across different Discount Name i.e. Discount September has Item 1234 and Discount October has 1234.

Join:

1. Item # (no other link possible as the discount #/name is not captured on the sales order)

Filter:

You will always choose a Discount Name via a filter so you'll always preselect a group of unique items.

This metric was "close" from another similar question but was non-performant and seemed to be duplicating quite often when I did get it to load:

SUMX(VALUES(Table1[YEAR]),[YEAR])-SUM('Table'[Year])

The calculated column that works in a many to one looks like this but of course, that does not work once many to many is the relationship:

Price Match Discount Price = IF(ROUND('Sales Detail'[Price],2) - ROUND(RELATED('Discounts'[ITEM_PRICE]),2) = 0.00, "Y","N")

Possibly a Filter or KeepFilter needed in the metric formula? 

[Anonymous]

Hi @USABB_Data 

Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @USABB_Data -you could create a bridge table with distinct Item # if needed, but that depends on your data structure. one more, If you are working on large datasets, it's crucial to avoid calculated columns or using RELATED in a many-to-many scenario, as it can lead to performance degradation.

Instead, keep calculations in measures to allow DAX to perform calculations dynamically based on filters.

Below is the measure for example:

Price Match Discount Measure =
IF (
ISFILTERED('Discount'[Discount Name]), 
CALCULATE(
IF (
ABS(SUM('Sales Detail'[Price]) - SUM('Discount'[Item_Price])) < 0.01, 
"Y", 
"N" 
),
FILTER(
'Sales Detail',
'Sales Detail'[Item #] IN VALUES('Discount'[Item #])
)
),
BLANK() 
)

Hope this helps 

[USABB_Data]

Thanks for the suggestion.  This did not return the correct results and was non-performant when added to a table visual with other columns.