Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Show Text for no data - Dax

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 "     Wor...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    Anonymous Not sure if it will help but here:

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

  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

    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]
    )

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

Anonymous 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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    Anonymous Not sure if it will help but here:

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

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

    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]
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Wrapping the measure in this worked. thank you