topic Re: Dynamic Text for a Drill Through in Desktop

Dynamic Text for a Drill Through

SusanRay — Wed, 09 Aug 2023 19:04:37 GMT

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 example, if I drill through after clicking on the dark blue portion of the Engineering bar, I would like the text in the sample below to end with "Engineering" and "Male"

Thoughts?

Re: Dynamic Text for a Drill Through

amitchandak — Thu, 10 Aug 2023 03:34:00 GMT

SusanRay , You can create a measure like below and use that in title

Meausre = "Student enrollment for" & selectedvalue(Table[semester]) & " for selectedvalue(Table[Year]) & selectedvalue(Table[Gender]])

Re: Dynamic Text for a Drill Through

SusanRay — Thu, 10 Aug 2023 16:19:50 GMT

I have been able to create the first part with "Semester," "Year," and "Color Options" as dynamic values in my text box. (Thanks)

However, I cannot figure out how to add the values that are used to focus the drill through.

For example, If I right-click on the dark blue portion of the Engineering bar and drill through, the table only shows data related to male students in the College of Engineering. I want my text box on the drill-through details page to state that so the user remembers that the data they are looking at is filtered on those values.

I guess a more general question is, what does P BI call the elements used to focus a drill-through?

Re: Dynamic Text for a Drill Through

SusanRay — Fri, 01 Sep 2023 18:20:28 GMT

amitchandak

Here is a little more detail about what I am trying to do:

The last value I need to include in the title changes depending on the Color Parameter. So, I cannot simply use selectedvalue(Table[Gender])

Since Color Parameter =

{

("Overall", NAMEOF('Enrollment'[Overall]), 0),

("Gender", NAMEOF('Student Bio Demo'[Gender]), 1),

("Race/Ethnicity", NAMEOF('Student Bio Demo'[Ethnicity Race Group]), 2),

("Minority", NAMEOF('Student Bio Demo'[Minority]), 3),

("Residency", NAMEOF('Student Bio Demo'[Residency level]), 4)

I tried

Drillthrough Title Piece = IF(Color Parameter = "Overall", "Overall", (If(Color Parameter = "Gender", selectedvalue('Student Bio Demo'[Gender]), (If(Color Parameter = "Race/Ethnicity", selectedvalue('Student Bio Demo'[Ethnicity Race Group]), (If(Color Parameter = "Minority", selectedvalue('Student Bio Demo'[Minority]), selectedvalue('Student Bio Demo'[Residency level]))))))))

But Power BI doesn't like this. Any suggestions?

Re: Dynamic Text for a Drill Through

gmsamborn — Fri, 01 Sep 2023 19:13:52 GMT

Does this help? I think it was just a matter of incorrectly placed parentheses.

Drillthrough Title Piece = IF( 'Color Parameter'[Value] = "Overall", "Overall", IF( 'Color Parameter'[Value] = "Gender", SELECTEDVALUE( 'Student Bio Demo'[Gender] ), IF( 'Color Parameter'[Value] = "Race/Ethnicity", SELECTEDVALUE( 'Student Bio Demo'[Ethnicity Race Group] ), IF( 'Color Parameter'[Value] = "Minority", SELECTEDVALUE( 'Student Bio Demo'[Minority] ), SELECTEDVALUE( 'Student Bio Demo'[Residency level] ) ) ) ) )

Re: Dynamic Text for a Drill Through

SusanRay — Mon, 11 Sep 2023 20:33:19 GMT

Thank you for the response, gmsamborn .

Unfortunately, that did not work. The error message says "The syntax for 'Value' is incorrect."

Re: Dynamic Text for a Drill Through

gmsamborn — Mon, 11 Sep 2023 21:50:53 GMT

Hi SusanRay

I was just going by your definition for [Drillthrough Title Piece]. In you definition, you refer to Color Parameter without using quotes (ie. 'table') or square brackets ( ie. [measure] ). I have no way of knowing if that is a measure or a table (or even a column).

Re: Dynamic Text for a Drill Through

SusanRay — Wed, 13 Sep 2023 13:18:18 GMT

Hello gmsamborn ,

I apologize if I wasn't clear. I am still very new to Power BI and Dax.

Color Parameter is just that, a parameter. ( a 'fields' parameter if that helps)

My use case:

I have one page with a stacked bar graph that changes the legend colors based on the Color Parameter selection (Gender, Race/Ethnicity or Residency, etc.).

A user can select a portion of a bar and drill through to another page. That page, of course, only shows data relevant to the particular bar and color selected.

I need a way to title the drill-through page that clearly indicates what was selected on the previous page (for example "Male" or "Asian" or "In-State"). The exact verbiage will vary depending on which parameter was chosen.

I am struggling to find the correct DAX syntax for that purpose.

Does any of that make sense?

Re: Dynamic Text for a Drill Through

gmsamborn — Wed, 13 Sep 2023 13:40:55 GMT

Hi SusanRay

Is there any way you could come up with a sample report with fictional data and any past attempts at a solution?

1) Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

Re: Dynamic Text for a Drill Through

SusanRay — Thu, 14 Sep 2023 14:31:59 GMT

gmsamborn,

I really appreciate your help with this.

A trimmed-down copy of my project is available here: https://drive.google.com/file/d/1VmMU6fkARoUMBoOB2gvbr6YokO6hh_xb/view?usp=sharing

I added some text boxes on the various pages describing what I hope to see and what I have tried so far.

I feel like I must be missing something obvious because I cannot be the only person with this project requirement.

Re: Dynamic Text for a Drill Through

gmsamborn — Thu, 14 Sep 2023 15:03:20 GMT

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] ) ) ) ) )

Re: Dynamic Text for a Drill Through

SusanRay — Thu, 14 Sep 2023 15:47:14 GMT

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?

Re: Dynamic Text for a Drill Through

gmsamborn — Thu, 14 Sep 2023 18:53:47 GMT

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

Re: Dynamic Text for a Drill Through

gmsamborn — Thu, 14 Sep 2023 18:00:56 GMT

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.)

Re: Dynamic Text for a Drill Through

SusanRay — Thu, 14 Sep 2023 18:03:49 GMT

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?

Re: Dynamic Text for a Drill Through

gmsamborn — Thu, 14 Sep 2023 18:57:11 GMT

SusanRay

My mistake with that last measure.

The last line should be returning _Logic not _ColorParm.

Sorry about that.

Re: Dynamic Text for a Drill Through

SusanRay — Mon, 18 Sep 2023 15:49:54 GMT

Thank You!!!