Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help! Create a relationship when a natural one does not exist

I have a table of the cost of items where there is a starting SKU and an ending SKU. Imagine it looks like this: Starting     Ending  Cost 1000           2000      1.00 2001           3000      2...
Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    Create a new calculated column in Table2 and use the DAX.

     

    Column =
CALCULATE (
    SUM ( 'Table (2)'[Units] ) * SUM ( 'Table'[Cost] ),
    FILTER (
        'Table',
        'Table (2)'[SKU Number] >= 'Table'[Starting]
            && 'Table (2)'[SKU Number] <= 'Table'[Ending]
    )
)

    If this helps, mark it as a solution

    Kudos are nice too.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Does the SKU number from table 2 not appear in any column on table 1? I'm not sure I understand what the Starting and Ending thing is. Is that a range of all the SKUs between 3001 and 4000? If that's the case, I would add a column in the query editor that creates a list of numbers in the range, then expand that to new rows, making a table that has a row for each SKU individually. I'll be back in a few minutes with some sample code...

Anonymous's avatar
Anonymous
Not applicable
6 years ago

In the query editor, add a custom column named SKU.

= List.Numbers([Starting], [Ending] - [Starting])

Next, at the top of the new column, you will see a little double arrow icon. Click on that and select "Expand to New Rows" and that will create a row for each SKU. Now load the table and you can create a relationship between the two tables.