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;ProdKe...
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
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])
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".
