Forum Discussion
Help with creating a measure to replace Calculated Columns
I am using Calculated Columns (in Power Pivot) to calculate the outstanding value on a PO for a Uk Warehouse.
Can this be done without using the Columns? I use calculated columns a lot and I am sure it is slowind the data model down.
Example
Column 1: PO Landed Costs GBP =RELATED(Product_Key[Landed Cost Sterling])
Column 2: PO_Q_UK =IF('PO_KEY'[Warehouse] = "UKMAIN", 'PO_KEY'[Outstanding Quantity], Blank())
Column 3: PO_UK_£ =IF(PO_KEY[PO_Q_UK]<>0,PO_KEY[PO_Q_UK]*PO_KEY[PO Land Cost GBP],BLANK())
Hi SamTaylor ,
I created some data:
PO_KEY：
Product_Key
As per the official Microsoft documentation, the RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous
https://docs.microsoft.com/en-us/dax/related-function-dax
You can use the CALCULATE function instead.
Here are the steps you can follow：
1. Create measure.
Measure1:
PO Landed Costs GBP = CALCULATE(MAX('Product_Key'[Landed Cost Sterling]),FILTER(ALL('Product_Key'),'Product_Key'[Index]=MAX('PO_KEY'[Index])))Measure2:
PO_Q_UK1 = IF(MAX('PO_KEY'[Warehouse]) = "UKMAIN",MAX('PO_KEY'[Outstanding Quantity]), Blank())Measure3:
PO_UK_£ = IF([PO_Q_UK1]<>0,[PO_Q_UK1]*MAX( PO_KEY[PO Land Cost GBP]),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi SamTaylor ,
I created some data:
PO_KEY：
Product_Key
As per the official Microsoft documentation, the RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous
https://docs.microsoft.com/en-us/dax/related-function-dax
You can use the CALCULATE function instead.
Here are the steps you can follow：
1. Create measure.
Measure1:
PO Landed Costs GBP = CALCULATE(MAX('Product_Key'[Landed Cost Sterling]),FILTER(ALL('Product_Key'),'Product_Key'[Index]=MAX('PO_KEY'[Index])))Measure2:
PO_Q_UK1 = IF(MAX('PO_KEY'[Warehouse]) = "UKMAIN",MAX('PO_KEY'[Outstanding Quantity]), Blank())Measure3:
PO_UK_£ = IF([PO_Q_UK1]<>0,[PO_Q_UK1]*MAX( PO_KEY[PO Land Cost GBP]),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly