Salesforce objects, multiple tables and work DATA-COLUMN from 2 tables.

[Anonymous]

Hi. 

Seeking your help!

I have next scenario:

Table A, Table B. 

I need to bring data Column from Table B to Table A, based on on a Dax formula. Only option i can think of is IF function but it does not work ( although there is relationship).

I used this scenario in the past and IF function worked but data was in the same table, now data is in 2 different tables and it does not work. 

Anyone knows of a different DAX function that could have same functionality? 

[lc_finance · Accepted solution]

Hi @Anonymous ,

It can work but you'll need an Index column on both tables, to link them to each other. Unless you have a different logic for linking them.

You can add an Index column in Power Query by clicking on Add Column -> Index Column.

Once the 2 tables are linked, you can use the RELATED DAX formula for it.

https://docs.microsoft.com/en-us/dax/related-function-dax

For example:

IF(RELATED('Table 2'[Rule])="Labour", [A],0 )

Let me know if it works for you.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Hi. 

It worked!!

My only mistake was that I was trying to related tables from One -To -Many. IT only works from Many -To- One.

[lc_finance]

Hi @Anonymous ,

good to hear that it worked!

If you need anything else, do not hesitate to ask,

LC

[Anonymous]

Hi @lc_finance . 

I have same scenario, and same problem.

So I have this Table A:

1. Date -Column

2. Name and Surname -Column. 

3. Hours- Column

Table B: 

1. Date -Column

2. Name and Surname - Column (relationship between Table A  from many- to- many)

Objective: To bring Hours-Column to table B. 

Related function doesnt work, i guess because its from many to many relationship.

Can anyone think of a function?

[lc_finance]

Hi @Anonymous ,

Indeed, if the relationship is Many to Many, then you need to use a RELATEDTABLE function instead of a RELATED function.

When you have a many to many relationship, you can have several related rows in the other table.

It's then up to you how to aggregate the multiple rows that you find from the related table.

You might then decide to add them up (SUM), average them (AVERAGE), etc

How do you want to aggregate the multiple rows?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[Anonymous]

I will try this solution next time. 

I managed to work with Calculate function(sum function and then I applied filter.

Thank you still, but will try you option next time and validated as a solution. 

[lc_finance]

Hi @Anonymous ,

I am glad you were able to find a solution.

Do not hesitate if you have more questions,

LC