MULTIPLICATION BETWEEN 2 TABLES

[ALE_P]

Hello everyone,

it's the first time I write.
I have this problem:

I don't want to leftjoin between table1 and table2, because table1 is 3 million rows and works with direct query.

I am looking for some suggestions to create a measure or a calculated column.

Thanks in advance.

Alex. 

[ALE_P]

[Anonymous]

Create columnn like this in first table

New columnn=LOOKUPVALUE('Table 2'[price],'Table 2'[ID],'Table'[ID])

Then simply do multiplication of Qty and price.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[ALE_P]

syntax: LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>)

this function returns a value, not a column, so i tried to make a measure, but the <search value> parameter is a value, and not a column.

Itried with DISTINCT, but "This function cannot be used to Return values into a cell or column".

the idea was good....

ps.: there is already a relationship many-to-one between Table1 and Table2.

[Anonymous]

Then simply create measure

New measure=sumx(table1,table1[col]*related(table2[col]))

Remember related function work for many to one and relatedtable work for one to many relationship.

Thanks
Pravin

[ALE_P]

Sorry, but as I wrote, LOOKUPVALUE it doesn't work.

[Anonymous · Accepted solution]

Hi @ALE_P 

Create simple measure as below,

Total = SUMX(Sales,Sales[Value 1]*RELATED('Table'[Price]))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hey

take a look at this solution . As u have a large datastet ( millions of rows that is better to create a measure and not calculated columns)