Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic Date title for Drilldown

Hi guys   So I have a line graph with Date along the x axis and a sum of paid invoices along the Y. Simples. The graph uses a DimDate table and I have it as a heirarchy for drilldown. I want the t...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi Anonymous ,

 

Try the following code, be aware that the order of the arguments is very important.

 

var =
"PAID INVOICES BY:" &
SWITCH (
    TRUE ();
    DISTINCTCOUNT ( Date[Month] ) = 1; "Day";
    DISTINCTCOUNT ( Date[Quarter] ) = 1; "Month";
    DISTINCTCOUNT ( Date[Year] ) = 1; "Quarter";
    "Year"
)

 

Be aware that for this calcultion I have a Month, quarter and year column on my table.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

MFelix thanks, this is looking a little better, but my title now just says "YEAR" on every level.

My DimDate table has calculated columns for MONTH QUARTER and YEAR: 
Treatment Year =YEAR([Date])

Treatment Quarter =QUARTER([Date])
Treatment Month = FORMAT([Date],"mmm"

 

My measure looks like this:

Title Paid Invoices by Treatment Period =

"PAID INVOICES BY: " &
SWITCH(
TRUE(),
DISTINCTCOUNT(DimDate[Treatment Month]) = 1, "MONTH",
DISTINCTCOUNT('DimDate'[Treatment Quarter]) = 1, "QUARTER",
"YEAR")
 
If I use a slicer with Month Year or Quarter columns in it, the dynamic title starts working. But, I want it to work when you drilldown on the visual, not apply a slicer. 
  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    As I refered the order is very important on the setup you are making this also if you check my formula I'm not picking up the current level for giving the title but the higher one, so for returning Month I'm checking if the quarter is only a single value.

     

    Try the following code.

     

    Title Paid Invoices by Treatment Period =

"PAID INVOICES BY: " &
SWITCH(
TRUE(),
DISTINCTCOUNT(DimDate[Treatment Month]) = 1, "Day",
DISTINCTCOUNT('DimDate'[Treatment Quarter]) = 1, "Month",
DISTINCTCOUNT('DimDate'[Treatment Year]) = 1, "Quarter",
"YEAR")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      For some reason just doesn't work. Maybe because my date calendar is my month and year, with no day. Ah well I can just have a hard coded title it's not the end of the world! Thanks for helping 🙂

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        Can you share a mockup file? In my tests I was struggling with the setup that why I refered that you need to do it in a specif order.