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

[gvg]

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

[TomMartens]

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

[Phil_Seamark]

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]
                    ) 

[gvg]

OK, this works. But will I be able filter this table by date?

[v-yuezhe-msft · Accepted solution]

@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