Forum Discussion

USABB_Data's avatar
USABB_Data
Helper II
1 year ago

Subtract two columns with Many to Many Relationship - Likely Measure

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? 
Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      USABB_Data
      Helper II
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.