cancel
Showing results for
Did you mean:
gvg Post Partisan

## DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

Hi there,

I am looking to build a measure that multiplies values in each row of fact table by corresponding value in another table. That table is unrelated. I know how to do this with related tables, but struggling to achieve same result with unrelated ones.

My fact table looks like this:

ID      Amount

10      100

20      120

10        50

30       40

30       20

My unrelated table with corresponding values looks like this:

ID      Discount

10      10%

20        0%

30       15%

I need to get resulting table like this:

ID      Amount    Discount

10      100           10

20      120             0

10        50             5

30       40              6

30       20              3

1 ACCEPTED SOLUTION

Accepted Solutions v-yuezhe-msft
Moderator

## Re: DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

@gvg,

Does  the Date column exist in the first table? If so, create the new table using the DAX below.

```Table = SELECTCOLUMNS(
FILTER(
CROSSJOIN(Table1,Table2),
'Table1'[ID]='Table2'[ID]
),
"ID",'Table1'[ID],
"Amount",'Table1'[Amount],
"Date",'Table1'[Date],
"Discount",'Table2'[Discount]
)```

Then create a column using the following formula in the new table. And you can filter the table by date.

`expected-discount = 'Table'[Amount]*'Table'[Discount]`  Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4 Phil_Seamark
Microsoft

## Re: DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

Hi @gvg

This calculated table is a quick way to get your result, but assumes you have an ID in the Discount table for every ID that exists in the Fact table

```Table 3 = SELECTCOLUMNS(
FILTER(
CROSSJOIN(Table1,Table2),
'Table1'[ID]='Table2'[ID]
),
"ID",'Table1'[ID],
"Amount",'Table1'[Amount],
"Discount",'Table2'[Discount]
) ```

Proud to be a Datanaut!

gvg Post Partisan

## Re: DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

OK, this works. But will I be able filter this table by date? v-yuezhe-msft
Moderator

## Re: DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

@gvg,

Does  the Date column exist in the first table? If so, create the new table using the DAX below.

```Table = SELECTCOLUMNS(
FILTER(
CROSSJOIN(Table1,Table2),
'Table1'[ID]='Table2'[ID]
),
"ID",'Table1'[ID],
"Amount",'Table1'[Amount],
"Date",'Table1'[Date],
"Discount",'Table2'[Discount]
)```

Then create a column using the following formula in the new table. And you can filter the table by date.

`expected-discount = 'Table'[Amount]*'Table'[Discount]`  Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Super User III

## Re: DAX measure to multiply qty for each row in fact table by a value in unrelated lookup table

Hey,

not sure if I'm missing something important, but to me it seems that a simple Lookup should be all you need. Create a calculated column in your Table1 like so

`LOOKUPVALUE('Table2'[Discount],Table2[ID],'Table1'[ID]) * 'Table1'[Amount]`

this pulls the Discount Value from Table2 into Table1 and multiplies the pulled value with the amount.

Hope this gives you another idea, where you do not need to compose a table-

Regards

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Announcements #### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community! #### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries. #### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now. Top Solution Authors
Top Kudoed Authors
Users online (1,408)