topic Re: Salesforce objects, multiple tables and work DATA-COLUMN from 2 tables. in DAX Commands and Tips

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

Anonymous — Wed, 30 Oct 2019 11:33:40 GMT

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?

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

lc_finance — Sun, 03 Nov 2019 22:46:04 GMT

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.

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

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

Anonymous — Wed, 13 Nov 2019 09:55:22 GMT

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.

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

lc_finance — Thu, 14 Nov 2019 22:08:30 GMT

Hi Anonymous ,

good to hear that it worked!

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

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

Anonymous — Mon, 18 Nov 2019 16:19:00 GMT

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?

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

lc_finance — Mon, 18 Nov 2019 22:42:00 GMT

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?

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

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

Anonymous — Wed, 20 Nov 2019 12:33:58 GMT

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.

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

lc_finance — Sun, 24 Nov 2019 21:26:28 GMT

Hi Anonymous ,

I am glad you were able to find a solution.

Do not hesitate if you have more questions,