Forum Discussion

AlexG3726's avatar
AlexG3726
Frequent Visitor
5 years ago

Column Chart axis change based on slicer

Hello, I am trying to create a column chart that changes based on a slicer. I built a slicer to display branch, processor, and sales rep. When I select say sales rep, I would like all of the sales rep to be displayed on the axis. I running into issues on the measure I would need to create to put into the axis for the chart.
 
Here is what I have so far: 
Selected Measure = IF(HASONEVALUE('Graph Slicer'[ID]),SWITCH(VALUES('Graph Slicer'[ID]),
1,'Table'[BranchName],
2,'Table'[ProcessorName],
3,'Table'[Sales Rep]))
 
The error I am getting is: A single value for column 'BranchName' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
Much Appreciated!
 
Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    AlexG3726 Well, I the code is definitely not right, you want this:

    Selected Measure = IF(HASONEVALUE('Graph Slicer'[ID]),SWITCH(MAX('Graph Slicer'[ID]),
1,MAX('Table'[BranchName])),
2,MAX('Table'[ProcessorName]),
3,MAX('Table'[Sales Rep])))

or

Selected Measure = SWITCH(SELECTEDVALUE('Graph Slicer'[ID]),
1,MAX('Table'[BranchName]),
2,MAX('Table'[ProcessorName]),
3,MAX('Table'[Sales Rep]),BLANK())

     

    That said, measures won't work for an Axis. You likely need this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titles-chart-types/m-p/1027881#M444

     

     

     

    • AlexG3726's avatar
      AlexG3726
      Frequent Visitor
      5 years ago

      Greg_Deckler  Thank you! The two measures didn't provide errors but were not able to be placed on the axis. I came accross that dynamic article you shared right before posting this actually! I wasn't able to apply anything out of it that was succesful.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        AlexG3726 It's more of a data trick than anything. You need a table that UNION's your three columns into a single column. You use that as your Axis and based upon the selection, you filter appropriately.