Optimizing DAX for Supplier Data: Handling Missing Values in Calculated Columns

Martin-Edan — Thu, 12 Sep 2024 15:07:38 GMT

Question: In the calculated column below, we are determining the Last Year Unit price for suppliers. However, new suppliers lack data, resulting in blank rows. I need to amend the logic in the column to populate these blank values with those from existing suppliers for the same material number. Our data contains pairs of suppliers, and we want to use the value from the supplier that has the data.

I have managed to create a ‘test’ secondary calculated column that provides the results (shown below). I need to figure out a way to do this all in one column.

Any help or suggestions would be greatly appreciated!

Main Calculated Column:

LYUnitPriceUSD_DEC =
VAR __LastYearKey = Table1[Column1] & "_" & Table1[Year]-1 & "_Q" & Table1[Quarter]

VAR __ExcelValue = LOOKUPVALUE(
Table2[PriceLastYear],
Table2[Key],
__LastYearKey)

VAR __SAPValue =
LOOKUPVALUE(
Table3[AveragePrice],
Table3[Key],
Table1[Column1] & "_" & Table1[Year]-1)

VAR __CurrentRateYearCode =
LEFT( RELATED(Table4[CurrencyYear]) , 3 )
& "_" &
IF(
Table1[Year] > YEAR( TODAY() ),
YEAR( TODAY() ),
Table1[Year])

VAR __RateMultiplication =
LOOKUPVALUE(Table5[RateMultiplier],
Table5[CurrencyYearKey],
__CurrentRateYearCode)

VAR __SAPValueCurrentRate = __SAPValue * __RateMultiplication

VAR __Result = COALESCE(__ExcelValue,__SAPValueCurrentRate)

RETURN __Result

Test Column for Reference:

Find Duplicate Value test =
VAR __Exclude = CALCULATETABLE( VALUES(Table6[SupplierName]), Table6[Exclude] = "Exclude" )
VAR __AlternateLYValue =
IF( NOT( ISBLANK(Table1[LYUnitPriceBudatUSD_DEC]) && NOT( Table1[SupplierName] IN __Exclude ) ),
Table1[LYUnitPriceBudatUSD_DEC],
CALCULATE(
MAX( Table1[LYUnitPriceBudatUSD_DEC] ),
FILTER( Table1,
Table1[Year] = EARLIER( Table1[Year] )
&& Table1[ArticleNo] = EARLIER( Table1[ArticleNo] )
&& NOT( ISBLANK( Table1[LYUnitPriceBudatUSD_DEC]))
&& NOT( Table1[SupplierName] IN __Exclude ) ) ) )

RETURN __AlternateLYValue

Disclosure: This question was assisted by AI.

Re: Optimizing DAX for Supplier Data: Handling Missing Values in Calculated Columns

lbendlin — Thu, 12 Sep 2024 23:25:17 GMT

Read about COALESCE

topic Optimizing DAX for Supplier Data: Handling Missing Values in Calculated Columns in DAX Commands and Tips

Optimizing DAX for Supplier Data: Handling Missing Values in Calculated Columns

Re: Optimizing DAX for Supplier Data: Handling Missing Values in Calculated Columns