Forum Discussion
SELECTEDVALUE Dax Formula for multiple columns
I have a built a stacked bar chart that shows a value for each employee for of the five years listed in the legend. (Graph #1)
I am attempting to create button to switch this view to quantity. (Graph #2)
I wrote a Dax formula to set up a Dynamic Metrics with buttons to switch between the two types of views, value and quantity, but for only one of the five years that I hope to include.
Value or Shares = switch(SELECTEDVALUE
(Control[ID]),1,SUM('2019 Active Employee Proj'[2019]),2,SUM('2019 Active Employee Proj'[2019 Projected Cumulative Shares]),SUM('2019 Active Employee Proj'[2019]))
Even though I understand my formula to read that the value is the default, the default in coming in as the quantity. The [2019] does represent value.
Is the formula written incorrectly for the default return (if neither option is selected in the slicer)?
I do not believe that Sum is necessary in the formula since there is only one value in the column for each employee, but it does return the correct amount in both the quantity and value versions of the graph.
Is there another function that would be more appropriate than the Sum function?
I was able to get this statement to work for one of the five years, 2019. (Graph #2)
In the source data, an Excel spreadsheet, each year is a separate column. For the original stacked bar chart (Graph #1) the five years (2019 – 2023, please see the legend) were simply stacked in the value portion of the visualizations.
Is there a way to write all five years into the Dax formula so that each year comes through in the switch graph with its own color and data label as in the original stacked bar chart (Graph #1)? Can a SELECTEDVALUE show multiple returns, one from each column?
LegendGraph #2
I appreciate any help you can offer on the bolded questions above.
JMD
Miguel,
I was able to resolve this matter by unpivoting the columns and merging tables.
Thank you for the input.
JMD
3 Replies
Hi jdowney ,
The best way to do this would be to unpivot the years columns in order to get a slicer or something similar to give you the calculation.
Having this in separated columns what you need is to add the year to the switch value so the measure should be similar to this:
Value or Shares = SWITCH ( TRUE (), SELECTEDVALUE ( Control[ID] ) = 1 && SELECTEDVALUE ( Table[Year] ) = 2019, SUM ( '2019 Active Employee Proj'[2019] ), SELECTEDVALUE ( Control[ID] ) = 2 && SELECTEDVALUE ( Table[Year] ) = 2019, SUM ( '2019 Active Employee Proj'[2019 Projected Cumulative Shares] ), SELECTEDVALUE ( Control[ID] ) = 1 && SELECTEDVALUE ( Table[Year] ) = 2020, SUM ( '2019 Active Employee Proj'[2020] ), SELECTEDVALUE ( Control[ID] ) = 2 && SELECTEDVALUE ( Table[Year] ) = 2020, SUM ( '2019 Active Employee Proj'[2020 Projected Cumulative Shares] ), ... )Be aware that you need to make a table with the year for the selection to match.
Hi Miguel,
Thank you for your response.
I tried unpivoting the columns but the result was multiplied by 5 because I had to unpivot both the value and value year columns along with the share and share year columns. Each employee was listed 5 times for each year. Is there another way to unpivot to avoid the redundancy?
Also, the result did not show any distinction for each year. It seems that the distinction of color by year (the legend) in other graphs is a result of the columns listed in the Field Values. Can this be done with a New Measure?
I attempted to write the formula you proposed for the multiple columns but was confused by what you meant by "= 2019" or "= 2020." Are you saying I need to put the column header here?
JMD
Miguel,
I was able to resolve this matter by unpivoting the columns and merging tables.
Thank you for the input.
JMD
