Replace Blank Value in a Matrix with Related Value from another table

[Charlie_Brown]

Dear Community,

I have built a matrix to compare PRICES of a list of ITEMS from REFERENCE PRICELIST with CONTRACT PRICELIST. My challenge is that if the ITEM does not exist in CONTRACT PRICELIST, the matrix cell is blank and I would like to replace the blank with the PRICE from REFERENCE PRICE .

The painful way to fix it, was to insert manually rows in the CONTRACT PRICELISTs in excel for all missing ITEMS with blank PRICE , and then replace the blank with an IF function in BI:

CORRECTED PRICE = IF( ISBLANK( 'CONTRACT PRICELIST'[PRICE]), RELATED( 'REFERENCE PRICELIST'[PRICE]), 'CONTRACT PRICELIST' [PRICE])

The above is working fine if the EXISTING CONTRACT contains a row with the Item and blank price, however does not work if the EXISTING CONTRACT file does not contain the ITEM I am searching for some the REFERENCE PRICELIST.

Manually entering rows in the excel files before loading them is becoming too time consuming. Is there a way to replace those blanks without inserting rows in the excel files?

[v-frfei-msft · Accepted solution]

Hi @Charlie_Brown ,

Please check the following steps as below.

1. Create a calculated table as below and make it related to the fact table.

ITEM = VALUES('Table'[Item])

2. Create a measure as below.

Measure = 
VAR cal =
    SUM ( 'Table'[value] )
RETURN
    IF (
        ISBLANK ( cal ),
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[PRI] = "REFERENCE PRICELIST" ),
            VALUES ( 'ITEM'[Item] )
        ),
        cal
    )