Show blank " " on CARDS when nothing is selected on a Graph with Drill down.

[New_Folder]

I have a table of dates and number of instances per date. I set up a drill down. I would like to show the Year when drilling down. Currently the option that I can see is Min - Max. I am not familiar with DAX, but can follow instructions.

The first table shows the Years and the second is the drilled down. As you can see, it does not show which Year was selected. I would like the Year to show either on the Title or on a separate Card visual.

[PaulDBrown]

First, go into the x-axis in the formatting pane and make sure the Type is set to coninuous.

Expand the visual as

go into the x-axis in the formatting pane and turn off concatenate labels.

Optionally, you can add a dynamic title to the visual to help guide your users. Create a measure along the lines of:

Year Title =
VAR _MinYear =
    CALCULATE ( MIN ( 'Calendar Table'[Year] ), ALL ( 'Calendar Table'[Year] ) )
VAR _MaxYear =
    CALCULATE ( MAX ( 'Calendar Table'[Year] ), ALL ( 'Calendar Table'[Year] ) )
VAR _Sel =
    CONCATENATEX ( VALUES ( 'Calendar Table'[Year] ), 'Calendar Table'[Year], ", " )
RETURN
    IF (
        ISFILTERED ( 'Calendar Table'[Year] ),
        "Sales for " & _Sel,
        "Sales from " & _MinYear & " to " & _MaxYear
    )

In the formatting pane, go into General -> Title and add the measure to the conditional formatting option, 

 Now you are ready to go. Just beware that the "Drilling" / Hierarchy ribbon can be confusing to users. (Here the title really helps)

1) "Go to next level of the hierarchy" displays aggregated data by month for all the years 

2) "Expand all down one level in the hiearchy" still displays data for all the years, but by month and year:

 3) By turning on the "drill-down" option, you can access a particular year data by clicking on a data point

Hope this helps

[amitchandak · Accepted solution]

@New_Folder , You can create a measure

if(isfiltered(Date[Year]), selectedvalue(Date[Year]), "All Year")

You can use this measure in title or card

[New_Folder]

I might miss a detail here, should I replace something on the formula you provided? Im getting this error,

I think I got it working. How do I format to remove the thousands comma separator?

ugh, nevermind, found it.. thank you very much awesome internet people.