topic Re: RELATED, multiplication from two different tables when values almost the same ... in DAX Commands and Tips

RELATED, multiplication from two different tables when values almost the same ...

Anonymous — Mon, 17 Feb 2020 14:21:13 GMT

Hello World,
Please give me some help/advice.

Table1
Product column Revenue Breakout

Product A 0,3

Product B 0,2
Product C 0,1

Table2
Product Column Price
Product A 130

Product B 120
Product CD 60
Product E 80

I add a column to Table1, named New Price
like
New Price = Table1[Revenue Breakout] * RELATED(Table2[Column Price])

So my table Table1 now looks like
Product Column, Revenue Breakout, New Price
Product A, 0,3, 39

Product B, 0,2, 24

B U T
I would like to have a third row in Table1 like

Product CD, 0,1, 6

How can I do this ?!?!?!

Re: RELATED, multiplication from two different tables when values almost the same ...

VasTg — Mon, 17 Feb 2020 14:51:46 GMT

Anonymous

Where does the value in bold comes from?

Product CD, 0,1, 6

Re: RELATED, multiplication from two different tables when values almost the same ...

Anonymous — Mon, 17 Feb 2020 14:59:34 GMT

From Table1 and Product C which is 0,1

6 are 0,1x60

Re: RELATED, multiplication from two different tables when values almost the same ...

Pragati11 — Mon, 17 Feb 2020 16:44:52 GMT

Hi Anonymous ,

First thing RELATED dax function works as a look-up function from one table to another. In you case, the common column in TABLE1 and TABLE 2 is - PRODUCT

- Unique values for PRODUCT column in Table1 --> Product A, Product B, Product C

- Unique values for PRODUCT column in Table2 --> Product A, Product B, Product CD, Product E

- Matching values for PRODUCT column in both the tables --> Product A and Product B

The dax caluclation that you have used is as follows:

New Price = Table1[Revenue Breakout] * RELATED(Table2[Column Price])

This will return matching value for Table1[Product] from Table 2. Not the ones for products which don't exist in Table1.

I am assuming you have joined Table1 and Table2 on PRODUCT column.

In order to get products that exist in Table2 as well, you may need to create rows in Table1 for the products that are in Table2 but not in Table1. In that way you get all the products in Table1. Further, when you use the above DAX for "New Price", it will return the calculation for all the products.

Let me know if that helps.

Thanks.