Dynamic Drill Month or specific Date

[KarenFingerhut]

Hi

Im wanting to drill through from a visual on either full month or specific date. If drill on full month I want it to say i.e. June 2021 and if they select a specific date I want it to say i.e. 06/06/2021.

I have tried numerous ways of getting the DAX to work but unfortuantely I cant seem to write the correct DAX

Any help would be really appreciated

Many thanks

Karen

[rbriga · Accepted solution]

Oh, it's the title!

Here's what works for me:

Dynamic Title =
VAR _Scope =
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Calendar'[Date] ), SELECTEDVALUE ( 'Calendar'[Date] ),
        HASONEVALUE ( 'Calendar'[Year Month] ), SELECTEDVALUE ( 'Calendar'[Year Month] ),
        "Time Period"
    )
RETURN
    "Drill through for " & _Scope

You can use this as a card, or in a text box by adding a value.

[KarenFingerhut]

Hi there

Thanks for the above. Sadly its not working for me. Here's my DAX

^ Dynamic Title =

VAR _Scope =

SWITCH (

TRUE (),

HASONEVALUE ( Footfall [full_date] ), SELECTEDVALUE (Footfall [full_date]),

HASONEVALUE (Footfall [year_month] ), SELECTEDVALUE ( Footfall [year_month] ),

"Time Period"

)

RETURN

"Drill through for " & _Scope

The year [year_month] is a conacat i've done in the SQL

I then placed it in a card visual on the backdata tab and tried to drill through (drill works) but all im getting in the card visual is this with no month year or date showing

Can you please advise what I have done wrong.

Thank you

Karen

[rbriga]

That's hard to say. It works in my testing enviorment. 

1. Is Footfall [full_date] the same date field you have on the visual you do the drillthrough from? Are you certain the visual isn't using a field from a seperate calendar table, or different date field?
2. Is Footfall [year_month] the same month field you have on the visual you do the drillthrough from?
3. Is the card in the drillthrough page, and the text is still wrong after you've done a test drillthrough?
4. In the drilltrhough page, is the card changing correctly when you slice manually by Footfall [year_month] and\or Footfall [full_date]?
5. Is the rest of the drillthrough page working properly- for example, passing all the filters?

[KarenFingerhut]

Ah my bad, I'd created the DAX as a new column instead of a measure. Works a treat. Thank you so much 🙂

Kind regards

Karen

[rbriga]

Hi Karen,

Can you post 3 screen shots that will help understand the problem?

1. The visual when you're about to drill on a full month
2. The visual when you're about to drill on a single date (is this a drill down from the full month?)
3. The drill through page, including the table fields (assuming it's a table) and drill through fields

Thank you

[KarenFingerhut]

Hi there 

So sorry for late reply and thanks for getting back to me

Here's the info you need - Just to say I dont have a problem with getting the drill through to work its the dynamic title that I cant get to work. 🙂

The below chart is by Month, so when they drill through to the back data page I want the dynamic title to say i.e. june 2021 

The below chart is on the same page but they can drill through on a specific date. So select 01 October 2021 then the dynamic title will say that date

So basically which ever chart they drill on will be reflected in the dynamic title (month or day)

Hope this makess ense.

Many thanks

Karen