Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a Calculated Column from Different Fact Tables (No relationship)

Dear,

I have 2 fact tables without relationship and I'm trying to create a calculated column in FatSales using one column from another table (FatProductPricing).

 

Ex:

 

FatSales

DateKey;ProdKey;Quantity
20190926;100;200

 

FatProductPricing

DateKey;ProdKey;Value
20190926;100;109.00

 

TotalSales = FatSales[Quantity] * FatProductPricing[Value]

 

I did it using merge, bringing the Value column to FatSales, but I have other calculations to do in the same situation. Is it possible to do it using DAX?

 

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

@Anonymous ,

 

Try using LOOKUPVALUE( ) 

Syntax
DAX

Copy
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])   
Parameters
Term	Definition
result_columnName	The name of an existing column that contains the value you want to return. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_columnName	The name of an existing column, in the same table as result_columnName or in a related table, over which the look-up is performed. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_value	A scalar expression that does not refer to any column in the same table being searched.
alternateResult	(Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK() when result_columnName is filtered down to zero value or an error when more than one distinct value.
Return value
The value of result_column at the row where all pairs of search_column and search_value have a match.

If there is no match that satisfies all the search values, a BLANK or alternateResult, if supplied, is returned. In other words, the function will not return a lookup value if only some of the criteria match.

If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error or alternateResult, if supplied, is returned.

 If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
Super User

@Anonymous ,

 

Try using LOOKUPVALUE( ) 

Syntax
DAX

Copy
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])   
Parameters
Term	Definition
result_columnName	The name of an existing column that contains the value you want to return. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_columnName	The name of an existing column, in the same table as result_columnName or in a related table, over which the look-up is performed. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_value	A scalar expression that does not refer to any column in the same table being searched.
alternateResult	(Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK() when result_columnName is filtered down to zero value or an error when more than one distinct value.
Return value
The value of result_column at the row where all pairs of search_column and search_value have a match.

If there is no match that satisfies all the search values, a BLANK or alternateResult, if supplied, is returned. In other words, the function will not return a lookup value if only some of the criteria match.

If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error or alternateResult, if supplied, is returned.

 If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello, is it possible to do a look up over two  fact tables that have a common column but no relationship. but the same invoice numbers in the Invoice Number column for both tables. A FactRawData table, and a FactDataWarehouse table. For An invoice number in Raw Data table, add a column which shows the invoice $ value from the Data Warehouse table? 

 

Assuming both tables, the invoice number column "Invoice Number", and sales value "Gross Sales".

 

watje255_ju_0-1643262977212.png

 

Anonymous
Not applicable

Works for me, thank you
 
Total = FatSales[Quantity] * LOOKUPVALUE(FatProductPricing[Value],FatSales[DateKey],FatProductPricing[DateKey],FatSales[ProdKey],FatProductPricing[ProdKey])
jdbuchanan71
Super User
Super User

@Anonymous 

Could I suggest adding a concatenated key to both tables the joining them?

Make a calculated columns in each table.

Sales Key = FatSales[DateKey] & FatSales[ProdKey]
Pricing Key = FatProductPricing[DateKey] & FatProductPricing[ProdKey]

Create the join then your total sales measure is like so.

Total Sales = SUMX ( FatSales, FatSales[Quantity] * RELATED ( FatProductPricing[Value] ) )
Anonymous
Not applicable

@jdbuchanan71,

 

This is a good workaround and really works, but Im trying to avoid create relationship between the fact tables. Maybe better than the LOOKUP function because of its performance, but for me LOOKUP is more practical in the moment.

Thanks for answering,

OK, then you calculated column will look like this.

Pricing Value =
LOOKUPVALUE (
    FatProductPricing[Value],
    FatProductPricing[DateKey], FatSales[DateKey],
    FatProductPricing[ProdKey], FatSales[ProdKey]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.