Joining of tables

[Anonymous]

I have a table called Promotions and another called Cost Center

Cost Center:

CC NO   etc

1

2

3

and Promotions is

ID   From CC_NO   To CC_NO

X          2                     1

Y          4                     5

Z          3                     4

Usuakky I do a left join between tables when there is a common promary key of CC_No. However, since this has a From CC_NO and To CC_No, I'm confused as to how to provide the join between the two tables. Is there any way for this?

Thank You!

[edhans]

You can. It will just make your DAX statements a bit longer sometimes.

Join the cost center table CC NO to both columns. Make the one active you will use most often and write your DAX as normal.

For calculations where you need the other, you'll need to use something like this:

Measure =
CALCULATE(
    SUM(Sales[Sales Dollars]),
    USERELATIONSHIP(CostCenter[CC No],Promotions[To CC_NO])
)

I assumed the inactive relationship there was between cost center and the TO-CC# field, and that you have some ficticious sales table also joined to your Cost Center table.

[Anonymous]

@edhans hey I was thinking to have a look up function for the one I'm not going to connect.
So I have CC_No in my table X and the attributes of these are in another table Y. So is there a way I can look up for these attributes based on this CC_no on the other table and bring all those values to table X?

[edhans]

You can use a custom column for this. I don't recommend it for performance reasons. You are better off doing a MERGE in Power Query before bringing it into the DAX model.

I would have to sit down and think about how to do it in a custom column. Normally you'd use RELATED() or RELATEDTABLE() to bring it in, but it has to have a relationship. You might be able to use TREATAS() to create a virtual relationship and then RELATED() to bring it in, but I'm just spitballing here. And in a Starbucks headed out so no time to tinker with it.

A merge in Power Query though would take about 10 seconds to create.

[edhans]

So I thought about it for a bit.

You could use this. It looks up the cost center in the Cost Center table in the From CC field of the Promotion table, then returns the From CC_No in the Promitions table. LOOKUPVALUE apparently needs no relationships. 

CC To 2 = 
LOOKUPVALUE(
    Promotions[To CC_No],
    Promotions[From CC_No],
    CALCULATE(
        MAX('Cost Centers'[CC No])
    )
)

If there is a relationship from Cost Center to Promitions (one to many) on the CC From field, you could use this to retrieve the CC To field. (or CC From field for that matter)

CC To = 
MAXX(
    RELATEDTABLE(Promotions),
    Promotions[To CC_No]
)

I'd still do it in Power Query if that was an option.

[Anonymous · Accepted solution]

Hey, I did a similar lookup by creating new columns for each of the attributes and it worked!