Data not coming as expected

[damit23183]

Hi,

I am trying to create MATRIX table with hierarcy on (Region, Office, Manager) and all other columns will have aggregate (Measure/Calculated Field) value.

However, I would like to show all values whether they have aggregate value or not. 

Source Table:

I am getting this result:

This is the formula i have applied to get correct count:

Total Number of Decision = CALCULATE(

COUNT('Table'[ID]),

Filter('Table', NOT(ISBLANK('Table'[Date])))

)

Expected Result:

I would like to see all Region, Office and Manager whether they have value or not. With the formula i am using i can only see who has value. 

It does look like i am making some mistake in formula but not sure. I tried by GROUPBY and ALLEXCEPT also but didn't work.

Thanks

[Greg_Deckler · Accepted solution]

Perhaps:

Total Number of Decision =
VAR __value = CALCULATE(
COUNT('Table'[ID]),
Filter('Table', NOT(ISBLANK('Table'[Date])))
)
RETURN
IF(ISBLANK(__value),0,__value)

[damit23183]

 Hi,

Quick question though,

Is there a way I can put blank instead of 0.

I did try with BLANK() instead of 0 but result was filtered out. I am getting expected result with 0 only, however i would like to put blank or empty cell instead of 0.

Thanks

[damit23183]

Hi Greg,

Thank you so much it worked perfectly.

Thanks