Forum Discussion

Fusilier2's avatar
Fusilier2
Helper V
1 year ago
Solved

Dynamic bar color

Hope somebody can help with this?

I have a slicer and a clustered bar chart.

 

The slicer shows a list of departments and the bar chart shows the results for each department of a survey question.

 

 

 

 

 

Is there any way of formatting the bar chart so that the color of a bar will change color if its corresponding value is selected in the slicer? So for example here,  as Adult Services is selected, it's bar color wil change color to red:

 

but if Finace and audit were selected its color would change to red.

 

 

 

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Using the same disconnected table but with the logic in the measure reversed

    Disconnected Total Revenue = 
CALCULATE (
    [Total Revenue],
    TREATAS ( VALUES ( 'Disconnected Category'[Category] ), Category[Category] )
)

Disconnected Conditional Bar Color = 
IF (
    SELECTEDVALUE ( 'Disconnected Category'[Category]  )
        IN VALUES ( Category[Category] )
            && ISFILTERED ( Category  ),
    "red"
)

    Please see the attached pbix

12 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Fusilier2 

    You’ll want to use a disconnected table for the categories. If you use the same table (or one that's related), selecting something like "Finance & Services" will filter the bar chart to just that one category. With a disconnected table, you avoid that and can still apply conditional formatting using a measure that refers to it.

     

    Please see the attached sample pbix. 

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Have you even validated your response before posting it?

    Why are you telling OP to create a field parameter table by adding all the deparment fields. It is clear in the screenshots that the departments are in a single column? And even if you meant measures by fields, why would you have OP to unnecessarily create one for each distinct department? And what's with those extra responses?

    • Natalie_iTalent's avatar
      Natalie_iTalent
      Administrator
      1 year ago

      Hello danextian,

      The response you commented on has been removed for being inaccurate. 

       

      Best,

      Natalie H.

      Community Manager

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      1 year ago

      Here is the path

       

      1 create a new table

       

      Selection = ALLNOBLANKROW ( Table[column] )

       

      where Table[Column] is the column where you have the values "Finance and audit", etc

       

      Leave the new table disconnected ftomr the model

       

      2 create the following measure

       

      Selection Color =
      IF (
                 SELECTEDVALUE( Table[column] ) =
                 SELECTEDVALUE( Selection[column]),
                 "Red",
                 "Blue"
      )
       
      3 use the above measure to conditionally format your bars
       

      If this helped, please consider giving kudos and mark as a solution

      me in replies or I'll lose your thread

      Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

      Consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI

  • Fusilier2's avatar
    Fusilier2
    Helper V
    1 year ago

    Thanks for your responses, very helpful and I can get it working if I add in a separated table, however the existing slicer is currently used to filter other visuals on the page so I would need a solution that uses the existing slicer.

    I can't upload my pbix file due to org data security.

    • danextian's avatar
      danextian
      Super User
      1 year ago

      Using the same disconnected table but with the logic in the measure reversed

      Disconnected Total Revenue = 
CALCULATE (
    [Total Revenue],
    TREATAS ( VALUES ( 'Disconnected Category'[Category] ), Category[Category] )
)

Disconnected Conditional Bar Color = 
IF (
    SELECTEDVALUE ( 'Disconnected Category'[Category]  )
        IN VALUES ( Category[Category] )
            && ISFILTERED ( Category  ),
    "red"
)

      Please see the attached pbix

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        1 year ago

        danextian very good

         

        I did not notice that the customer was ok with hiding the not selected items in the slicer in the below table. 

         

        Very good solution!