cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Post Partisan
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
Moderator 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]

1.PNG2.PNG

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.

View solution in original post

4 REPLIES 4
Microsoft 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]
                    ) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gvg Post Partisan
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?

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

1.PNG2.PNG

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.

View solution in original post

Super User III
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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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