topic Re: Dynamic Drill Month or specific Date in DAX Commands and Tips

Dynamic Drill Month or specific Date

KarenFingerhut — Mon, 22 Nov 2021 06:33:26 GMT

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

Re: Dynamic Drill Month or specific Date

rbriga — Mon, 22 Nov 2021 06:42:36 GMT

Hi Karen,

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

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

Thank you

Re: Dynamic Drill Month or specific Date

KarenFingerhut — Tue, 23 Nov 2021 09:07:36 GMT

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

Re: Dynamic Drill Month or specific Date

rbriga — Tue, 23 Nov 2021 10:01:13 GMT

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.

Re: Dynamic Drill Month or specific Date

KarenFingerhut — Wed, 24 Nov 2021 06:42:20 GMT

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

Re: Dynamic Drill Month or specific Date

rbriga — Wed, 24 Nov 2021 08:15:59 GMT

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

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?
Is Footfall [year_month] the same month field you have on the visual you do the drillthrough from?
Is the card in the drillthrough page, and the text is still wrong after you've done a test drillthrough?
In the drilltrhough page, is the card changing correctly when you slice manually by Footfall [year_month] and\or Footfall [full_date]?
Is the rest of the drillthrough page working properly- for example, passing all the filters?

Re: Dynamic Drill Month or specific Date

KarenFingerhut — Wed, 24 Nov 2021 08:27:30 GMT

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