Show Text for no data - Dax

[Selded]

I need a dax that will retun text "RDO" for the date that work is showing blank in the below snip. To show the blank i used show data with no values on the date. 

Work measure below "

Work = CALCULATE(SUMX(Mx_VEW_DailyAttendance,IF(Mx_VEW_DailyAttendance[Total Hours]>8,12)))

Many thanks for the help.

[Greg_Deckler · Accepted solution]

@Selded Not sure if it will help but here:

Any chance you can share sample data as text so that I can mock this up?

[Ashish_Mathur]

Hi,

It is not a good practise to have formulas return mixed data types.

[Greg_Deckler]

@Selded Well, you could create a measure like the following perhaps:

Measure = 
  VAR __Work = SUM( 'Table'[Work] )
  VAR __Result = IF( ISBLANK( __Work ), "RDO", __Work & "" )
RETURN
  __Result

You may also need to change the Value filter behavior (auto-exist) for your Semantic model on the Model view.

[Selded]

how do i change the value filter behaiour? i tried the dax but is showing RDO multiple time other than for just the selected date with blank.

[danextian · Accepted solution]

Hi @Selded 

When a measure is forced to display something other than its actual value when blank, the formula engine performs a full cross-join of the dimension tables in the visual, evaluating each row explicitly. This can result in unexpected data points appearing. For example, a previously removed ID (e.g., one terminated in 2013) may still appear for a selected period, such as late February, but with custom values. That's why you're seeing RDO for those that are not within the slicer selection. You can add a condition to your measure to show only when a date is within the selected range. 

VAR MinDate =
    CALCULATE ( MIN ( 'calendar'[date] ), ALLSELECTED ( 'calendar' ) )
VAR MaxDate =
    CALCULATE ( MAX ( 'calendar'[date] ), ALLSELECTED ( 'calendar' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'calendar'[date] ) >= MinDate
            && SELECTEDVALUE ( 'calendar'[date] ) <= MinDate,
        [rdo measure]
    )

[Selded]

Wrapping the measure in this worked. thank you

[Greg_Deckler · Accepted solution]

@Selded Not sure if it will help but here:

Any chance you can share sample data as text so that I can mock this up?