Forum Discussion

AlixB's avatar
AlixB
Frequent Visitor
4 years ago
Solved

Calculated column that references another table column if current value is blank

I have a column in a table where some values are blank and need to be populated with a value from another table. 

the Hours YTD value is what is needed to populate the blanks

 

It needs to be done as a calculated column/measure as needs to be dynamic, the replacement value is based on the date filter.

 

There is a relationship between both tables which are date columns. However the blank fields have blank date fields too. There is a blank option in the date slicer which allows for blanks to show in the table.

I've tried the below abut it doesn't work as there is nothing to link the tables so returns nothing.

Hours Available =
IF(CALCULATE(SUM('Report Reliability_YearlyTotal'[TimeAvailable])) = BLANK(),
AVERAGE('Date'[DimDateMonthHours]),
CALCULATE(SUM('Report Reliability_YearlyTotal'[TimeAvailable])))
 
Any help would be appreciated
Need Help
  • AlixB's avatar
    AlixB
    4 years ago

    lbendlin 

    I actually solved the issue in SQL so that my source data has the information for the last 5 years, but thanks for looking at it

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    if you need it dynamic then it cannot be a calculated column - it has to be a measure.

     

    How are your rows sorted, or does that not matter?

     

    Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good, or provide a sample pbix) .

    • AlixB's avatar
      AlixB
      Frequent Visitor
      4 years ago

      lbendlin 

      I actually solved the issue in SQL so that my source data has the information for the last 5 years, but thanks for looking at it