For this workout, you are asked to create the DAX necessary to allow the user to select the year(s) to highlight in the column chart as well as select the color of that highlighting. You can modify/augment the data model provided if needed.
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 Thursday, March 23, 2023, and the author’s solution will be posted on Sunday, March 26, 2023.
I created the two disconnected table for the filter year and col and build my measure around that as shown in the below image and with the help of that i am able to archive the answer
Col =
VAr sel_col= SELECTEDVALUE('Col slicer'[Col])
VAR Sel_year= SELECTEDVALUE(Dates[Year])
VAr tb= VALUES('Date Slicer'[Year])
VAR con = if (Sel_year in tb ,sel_col)
return
con
I created two disconnected tables in Power Query, one for Years referenced off of the Dates table, and one for Colors, created through via Enter Data.
Then used the following measure to set the conditional formatting for the columns in the chart:
CF Color =
VAR SelColor =
SELECTEDVALUE( Colors[Color], "Black" )
VAR SelYrs =
VALUES( 'Disconn Years'[Year] )
VAR Result =
IF(
AND(
ISFILTERED( 'Disconn Years'[Year] ),
SELECTEDVALUE( Dates[Year] ) IN SelYrs
),
SelColor,
"Black"
)
RETURN Result
The key here is deciding what you want the slicer/visual to do when no years are selected? The default behavior is to treat that the same as selecting all the years. However, I wanted the visual to be all black when no years were selected, thus the additional ISFILTERED( ) function in my DAX measure…
@Paul.Gerber - great workout. Here was my approach:
Summary
First, I created a simple table with one column that has the three colors. I just used the names of the colors versus the hex codes. This table is used as the slicer for the user to choose which color. The formatting for this slicer was set to only allow one selection.
Second, I created a table to generate the years to be used as another slicer. For this table, I use some DAX to dynamically create the table on the presumption that it would be possible for more dates to arise in future. So the DAX code for this was:
I got to it a couple of days late but I did it . My approach was relatively simple. Mainly because I’ve never done this before. It was definitely a good brain teaser for me.
. My approach was relatively simple. Mainly because I’ve never done this before. It was definitely a good brain teaser for me.