DAX Workout #24 - Highlighted Value in a Line Chart

In this workout, you will create a dynamic line chart that will only highlight the value for that month selected. In addition, you will need to create a parameter to change the Y-axis for Total Costs and Total Sales.

So, there will be field parameters, conditional formatting, and some creativity involved in this workout.

Best of luck to you!

DAX _24 - Hightlighted Values in Line Chart 06112023.pbix (693.9 KB)

Submission

Load the supplied data file into a new Power BI file, create your solution, and reply to this post. Upload a screenshot of your solution along with the DAX measure. Please format your DAX code and blur it or place it in a hidden section.

Period
This workout will be released on Sunday, June 11, 2023, and will end on Saturday, June 17, 2023. But you can always come back to any of the workouts and solve them.

There is a subtlety in the combination of SWITCH with field parameters, but I have already come across this.
And so - a disabled table of months and a switch instead of a title.

i am not sure how to mask a pbi file , posting my solution below
DAX _24 - Hightlighted Values in Line Chart 06112023 (1).pbix (701.8 KB)

Hi,
my first ever submission to a workout here on EDNA. :blush:

The trick that I used is was to control the transparency of the data labels as well as the markers with a measure (the markers being the result of a column-chart-turned-line-chart with conditionally formatted column colors).

For me it all came down to the “Selected Month Highlight” measure, capturing the user’s month selection in an independent slicer. This was the hardest part since I wanted this to work with multiple month and parameter selections.

I’d be really interested to learn other ways to achieve this, but for me this is what worked:

Selected Month Highlight measure

VAR _Slicer = ALLSELECTED( ‘Month Short’[Month Short] )

RETURN
IF(
ISCROSSFILTERED( ‘Month Short’[Month Short] ),
IF(
MAX( ‘Date’[Month Short] ) IN _Slicer,
1,
0
),
0
)

I then used the output of this measure (0 for unselected, 1 for selected months) for conditional formatting with HSLA alpha values (0 for 0, and 1 for 1). The 0 alpha value ensures that the data label and marker for unselected months are invisible.

SWITCH logic for color transparency control via HSLA

SWITCH(
TRUE(),
[Selected Month Highlight] = 1, “hsla( 16 , 100% , 42% , 1 )”,
“hsla( 16 , 100% , 42% , 0 )”
)

For the chart title I used an additional text column named Title Helper in the parameters table which was essential to make SELECTEDVALUE work. (This was also a big lesson.)

"Title Helper" column

Parameter = {
(“Total Costs”, NAMEOF(‘​Measures’[Total Costs]), 0,“Total Costs”),
(“Total Sales”, NAMEOF(‘​Measures’[Total Sales]), 1, “Total Sales”)
}

…then changed the name of the fourth column to “Title Helper”

Chart title measure

CONCATENATE(SELECTEDVALUE(Parameter[Title Helper] ,“Total Costs and Sales”)," by Month")

Thank you @Paul.Gerber for the exercise, I learnt a lot from this!

2 Likes

Hi everyone.
I did this challenge with a different solution.
and here is a screenshot of it with some


kind of help.
thanks again @Paul.Gerber

1 Like

Hello
DAX _24 - Hightlighted Values in Line Chart 06112023.pbix (705.1 KB)

This is my 1st submission to a DAX workout here on EDNA.
Here are the Screenshot of the Solution is attached.
Thanks @Paul.Gerber

2 Likes

Hi to all ,
It is really a good idea to Highlight multiple month values at a time , it will also covered the highlighting single value also , Thanks to @Noemi

Here is the some glimpse of my submission

Here is the Full video about the working of report

2 Likes

1 Like

1 Like

Hi All,

I have got everything right in this workout but unable to get the red dot for the selected month.
Please help.

Hello @rishug2811 I am so happy to see you and others doing the DAX workouts.

Do you have a measure for highlighting your value?

In the formatting section, this is what I show:

Maybe that is where you are stuck. Share your file if you like.

Paul