Forum Discussion

SusanRay's avatar
SusanRay
Icon for Helper I rankHelper I
3 years ago
Solved

Dynamic Text for a Drill Through

I have created a dynamic text box for the top-level page that changes based on slicer values.   I am struggling with creating a similar text box for the drill-through destination page.   For...
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    SusanRay 

    My mistake with that last measure.

    The last line should be returning _Logic not _ColorParm.

    Sorry about that.

gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

Hi SusanRay 

After looking at your pbix, I'm unsure how/where you are using [Drillthrough Title Piece].

 

Regardless, would this help?

Drillthrough Title Piece = 
VAR _ColorParm = SELECTEDVALUE( 'Color Parameter'[Color Parameter] )
RETURN
IF(
    _ColorParm = "Overall",
    "Overall",
    IF(
        _ColorParm = "Gender",
        SELECTEDVALUE( 'Student Bio Demo'[Gender] ),
        IF(
            _ColorParm = "Race/Ethnicity",
            SELECTEDVALUE( 'Student Bio Demo'[Ethnicity Race Group] ),
            IF(
                _ColorParm = "Minority",
                SELECTEDVALUE( 'Student Bio Demo'[Minority] ),
                SELECTEDVALUE( 'Student Bio Demo'[Residency level] )
            )
        )
    )
)
SusanRay's avatar
SusanRay
Icon for Helper I rankHelper I
2 years ago

Hi gmsamborn ,

If you look at the Enrollment Count Detail Page,  you will see a text box that includes the phrase (Color Parameter Selection). That is where I hoped to put the "Drillthrough Title Piece."   (My bad. I wasn't consistent in my naming.)

 

Power BI did not throw an error when I created a measure using your proposed code. ðŸ™‚

But when I tried to 'add dynamic value' to the text box I am using for the title on the 'detail' page it wouldn't accept it. ðŸ˜•

 

 

I then tried to add it as a table or a card, but it gave me a "Couldn't load the data for this visual" error. ðŸ˜•

 

Specifically, it says, 

 

 

As I said, I am very new to Power BI. This is supposed to be a Measure, right?  Does it matter where that measure sits in my data?

 

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    The following link refers to that error.

     

    https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

     

    Maybe try this...

     

    Drillthrough Title Piece = 
VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( 
            'Color Parameter', 
            'Color Parameter'[Color Parameter],
            'Color Parameter'[p_Overall Fields]
        ),
        "_Color",
            'Color Parameter'[Color Parameter]
    )
VAR _ColorParm = 
    IF(
        COUNTROWS( __SelectedValue ),
        MAXX(
            __SelectedValue,
            [_Color]
        )
    )
VAR _Logic =
    IF(
        _ColorParm = "Overall",
        "Overall",
        IF(
            _ColorParm = "Gender",
            SELECTEDVALUE( 'Student Bio Demo'[Gender] ),
            IF(
                _ColorParm = "Race/Ethnicity",
                SELECTEDVALUE( 'Student Bio Demo'[Ethnicity Race Group] ),
                IF(
                    _ColorParm = "Minority",
                    SELECTEDVALUE( 'Student Bio Demo'[Minority] ),
                    SELECTEDVALUE( 'Student Bio Demo'[Residency level] )
                )
            )
        )
    )
RETURN
    _Logic     // corrected

     

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    Hi SusanRay 

    I for got to mention that when you are setting up your dynamic value, don't forget to use [ and ] (ie. [Drillthrough Title Piece] )

     

    Also, the location of a measure doesn't matter.  (I usually set up an empty table to put them in.)

     

  • SusanRay's avatar
    SusanRay
    Icon for Helper I rankHelper I
    2 years ago

    gmsamborn ,

     

    We are getting closer. 

    An updated file is available here:  https://drive.google.com/file/d/1AkwWiwWJlF3HzduyjFATpsp33aka8r_q/view?usp=sharing

     

    I was able to create the measure using your code without a problem, and by adding the [ ] I got the dynamic text added to the text box.  But it isn't giving me the information I am looking for.

     

    Rather than repeating the Color Parameter, which it does now, I want it to show which aspect of the selected measure was chosen for the drill through. (For example, 'Female' for Gender. OR 'Asian' for Race/Ethnicity. OR 'International' for Residency')

     

    Does that make sense?

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    SusanRay 

    My mistake with that last measure.

    The last line should be returning _Logic not _ColorParm.

    Sorry about that.

  • SusanRay's avatar
    SusanRay
    Icon for Helper I rankHelper I
    2 years ago

    Thank You!!!