Replacing blank values with "-" in matrix visual

[atasgao]

Hello Everyone, 

So, I want replace the blank cell with the "-".  

I am using multple formula to filter out the field. 

YTD Budget Hours = if(ISFILTERED(FactLaborActualHourBV[EmployeeName])|| ISFILTERED(FactLaborBudgetHourBV[BudgetLaborHour]),BLANK(),FactLaborBudgetHourBV[BudgetHourYTD]) so, that it does not show the whole list of employees. 

To calculate the BudgetHourYTD I am using this below formula

BudgetHourYTD = CALCULATE([M_BudgetLaborHour],FILTER(ALL( DimDate[Month] ), DimDate[Month] <= IF(Day(Now())<=15,Month(Now())-2,MONTH(Now())-1))). 

How can I add "-" if blank?

Thanks for your help in advance

Regards, 

Aakanksha 

[AllisonKennedy]

@atasgao 

Depending on your data model you can try: 

- Measure  = IF(ISBLANK ( [BudgetHourYTD], "-", [BudgetHourYTD]) )

Note that will be a variable data type measure so can't be used in some totals, calculations, etc. It may also give you more rows in your matrix than you wish so you may need to add further filters into your IF statement.

[atasgao]

@AllisonKennedy It is populating for all the records. 

[v-kkf-msft]

Hi @atasgao ,

If the Business Unit rows you want to hide remains hidden after the replacement, then you can try the following way.

1. Create measure to filter out the rows of Business Unit that are not all null.

Measure = 
var unit = VALUES ( FactLaborActualHourBV[Employee Labor Business Unit] )
var NotBlankRows = 
    CALCULATETABLE (
        VALUES ( FactLaborActualHourBV[Employee Labor Business Unit] ),
        FILTER (
            ALLSELECTED ( FactLaborActualHourBV[Employee Labor Business Unit] ), 
            [YTD Budget Hours] <> BLANK()
                || [YTD Actual Hours] <> BLANK()
                || [Annual Budget Hours] <> BLANK()
                || [YTD Hours Variance] <> BLANK()
        )
    )
return 
    COUNTROWS ( INTERSECT ( unit, NotBlankRows ) )

2. Then use the measure to filter the Business Unit column in the visual.

3. Then replace the blank values.

NonBlank YTD Budget = COALESCE ( [YTD Budget Hours], "-" )

NonBlank YTD Actual = COALESCE ( [YTD Actual Hours], "-" )

4. The final result is:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[atasgao]

@v-kkf-msft It's working but the problem is under the employee labor business unit it is giving all the employee name irrespectively of wheater we have data or not. 

Thanks, 

Aakanksha 

[v-kkf-msft · Accepted solution]

Hi @atasgao ,

Please try the following formula:

ALLBlankRows = 
[YTD Budget Hours] = BLANK ()
    && [YTD Actual Hours] = BLANK ()
    && [YTD Hours Variance] = BLANK ()
    && [Annual Budget Hours] = BLANK ()

Replace_YTD_Budget = IF ( [ALLBlankRows], BLANK (), COALESCE ( [YTD Actual Hours], "-" ) )

Replace_YTD_Actual = IF ( [ALLBlankRows], BLANK (), COALESCE ( [YTD Actual Hours], "-" ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[atasgao]

@v-kkf-msft This works! Thanks