Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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] )
OK, this works. But will I be able filter this table by date?
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |