Return blank in dax measure if data is missing

[chulpanvl]

Hello!

I have IndicatorsData table with historical data of actual coefficients across different indicator names.

I want to create a table visual, which will return the average actual coefficient for each indicator name, but for the last selected year only. So, if there is a multiple selection in a year slicer, the table should display the average coefficient only for the maximum of selected years. I created a measure 

last_selected_year = MAXX(ALLSELECTED('indicatorsData'[year]),'indicatorsData'[year]).

However, there are missing data. And of there is no data for last_selected_year for a particular indicator, I want the table visual to display nothing.

I created a measure for calculating the avg coefficient. But in case of missing data it returns last non blank value, while I want it to return blank or at least 0.

actual_coeff_last_year = CALCULATE(AVERAGE(indicatorsData[actual_coeff]), FILTER(indicatorsData, indicatorsData[year] = [last_selected_year]))

Pbix file to play with.

[Ahmedx]

read a book "Power BI DAX Simplified"  :- Chapter 7: Caution When Using Variables in DAX and Power BI

[Ahmedx · Accepted solution]

Is this what you are looking for?

[chulpanvl]

yes, exactly! omg thanks!

could you pls explain why if I create a measure "last_selected_year" outside the "actual_coeff_last_year", the latter behaves differently than if we would create a varibale "last_selected_year" inside the "actual_coeff_last_year"? So counterintuitive...