Help, measure to calculate if something is occupied breaks slicers

[aTChris]

Hi, im struggling to create a measure that will allow me to display if an asset is occupied or not.

I have a list of contracts that related to an asset. I create a table of contracts per month using a function so that I can plot the contract value against the asset name per month. That works fine.

The issue I have is that I want to show if an asset is occupied on another matrix so I created the following measure

Occupied = IF(Contracts[Revenue]=BLANK(),"0","1")

This breaks the relationship's of the matrix. The matrix displays all assets irrespective of what slicer is applied.

pbix file.

[v-jayw-msft]

Hi @aTChris ,

How about modify the measure as below?

Occupied = IF(Contracts[Revenue]=BLANK(),blank(),"1")

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[aTChris]

Thanks @v-jayw-msft 

It helps in PBI but what I didnt mention was that I want to use the dataset in excel as well. When using Analyse in Excel I dont have the option of showing zero values. Im trying to insert the 0 into all vacant assetts using the measure to force the pivot to display all assets and not just the contracted ones.

The only option I can think of it inserting zero value data into the contracts table for every month and updating the current measures to exclude the zero value data in the measures.