topic Matrix Values Reflecting Blank Instead of 0 in DAX Commands and Tips

Matrix Values Reflecting Blank Instead of 0

PBInewbie17 — Wed, 05 Jul 2023 18:40:21 GMT

I have a Power BI Matrix with Rows comprised of PHA_CD_NM and the DVLPT_NUM within each PHA_CD_NM. I have three separate measures I’ve included as columns within the Matrix. The problem is that Average Vacant Days (DDA) and Average Vacant Days (Non DDA) are calculating as blank values instead of 0 (when applicable). Can anyone help me adjust these formulas so blank items appear as 0? The formulas for each measure are below.

Average Vacant Days =

VAR AvgVacDays =

DIVIDE(SUM('F_T_UNIT'[VAC_DAYS]), SUM('F_T_UNIT'[UNIT_CNT]))

VAR IsBlankAvgVacDays =

ISBLANK(AvgVacDays)

RETURN

IF(

IsBlankAvgVacDays,

IF(

ISINSCOPE('D_PHA'[PHA_CD_NM]),

IF(

NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]),

AvgVacDays

BLANK()

AvgVacDays

)

Average Vacant Days (DDA) =

VAR AvgVacDaysDDA =

CALCULATE(

[Average Vacant Days],

'F_T_UNIT'[UNIT_DDAPP_INDR] = "Y"

)

VAR IsBlankAvgVacDaysDDA =

ISBLANK(AvgVacDaysDDA)

RETURN

IF(

IsBlankAvgVacDaysDDA,

IF(

ISINSCOPE('D_PHA'[PHA_CD_NM]),

IF(

NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]),

AvgVacDaysDDA

BLANK()

AvgVacDaysDDA

)

Average Vacant Days (Non DDA) =

VAR AvgVacDaysNonDDA =

CALCULATE(

[Average Vacant Days],

'F_T_UNIT'[UNIT_DDAPP_INDR] <> "Y"

)

VAR IsBlankAvgVacDaysNonDDA =

ISBLANK(AvgVacDaysNonDDA)

RETURN

IF(

IsBlankAvgVacDaysNonDDA || AvgVacDaysNonDDA = BLANK(),

IF(

ISINSCOPE('D_PHA'[PHA_CD_NM]),

IF(

NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]),

AvgVacDaysNonDDA

)

Re: Matrix Values Reflecting Blank Instead of 0

lbendlin — Sun, 09 Jul 2023 18:33:23 GMT

Add 0 to your measure.

In general, To report on things that are not there you need to use disconnected tables and/or crossjoins

Re: Matrix Values Reflecting Blank Instead of 0

PBInewbie17 — Mon, 10 Jul 2023 15:29:41 GMT

Can you please elaborate? Where would I add the 0? I am still learning. Thanks!

Re: Matrix Values Reflecting Blank Instead of 0

lbendlin — Mon, 10 Jul 2023 16:00:09 GMT

Average Vacant Days (Non DDA) = VAR AvgVacDaysNonDDA = CALCULATE( [Average Vacant Days], 'F_T_UNIT'[UNIT_DDAPP_INDR] <> "Y" ) VAR IsBlankAvgVacDaysNonDDA = ISBLANK(AvgVacDaysNonDDA) RETURN 0 + IF( IsBlankAvgVacDaysNonDDA || AvgVacDaysNonDDA = BLANK(), IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDaysNonDDA ), 0 ), AvgVacDaysNonDDA )

Something like this. It's more of a hack. Would be better if your measure would not run into such a scenario.