Forum Discussion
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?
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
6 Replies
- jdbuchanan71
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] ) )
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,- jdbuchanan71
OK, then you calculated column will look like this.
Pricing Value = LOOKUPVALUE ( FatProductPricing[Value], FatProductPricing[DateKey], FatSales[DateKey], FatProductPricing[ProdKey], FatSales[ProdKey] )
- Nathaniel_C
Community Champion
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- Works for me, thank youTotal = FatSales[Quantity] * LOOKUPVALUE(FatProductPricing[Value],FatSales[DateKey],FatProductPricing[DateKey],FatSales[ProdKey],FatProductPricing[ProdKey])
watje255_juHelper III
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".
