Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
My mistake with that last measure.
The last line should be returning _Logic not _ColorParm.
Sorry about that.
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
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] )
)
)
)
)
Thank you for the response, @gmsamborn .
Unfortunately, that did not work. The error message says "The syntax for 'Value' is incorrect."
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).
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?
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.
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.
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] )
)
)
)
)
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?
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.)
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
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?
My mistake with that last measure.
The last line should be returning _Logic not _ColorParm.
Sorry about that.
Thank You!!!
@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]])
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?
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |