Forum Discussion

AO90's avatar
AO90
New Member
3 years ago
Solved

Highlight 100% Stacked Bar Based on Slicer Selection

Hello,

I have a provider slicer on my data set. I want the end user to have the ability to filter to the desired provider's data but instead of filtering the 100% stacked bar I want it to highlight the selected provider(s). To do this I have created a duplicate of my data table. The stacked bar chart contains on the x-axis the responsible providers name (usp_EM_Code_Dup[NoteResponsibleProvName]) from a duplicate data table. On the Y-Axis is a count of CPT codes from the duplicate data table(usp_EM_Code_Dup[CPT_Code]). My slicer is from the original data table (usp_EM_Code[NoteResponsibleProvName]).

 

I have been able to create a simple highlight using two colors:

 

Provider_Highlight = IF (ISFILTERED(usp_EM_Code) && VALUES(usp_EM_Code_Dup[NoteResponsibleProvName]) IN VALUES (usp_EM_Code[NoteResponsibleProvName]), "Green", "Grey")

 

I have been able to make this code work for an individual provider, accounting for all CPT Codes:

 

Provider_Highlight = IF (usp_EM_Code_Dup[NoteResponsibleProvName]) = "Provider, Name", usp_EM_Code_Dup[CPT_Code], "XX" & usp_EM_Code_Dup[CPT_Code])

 

but have not been able to make it work for the provider selected by the filter:

 

Provider_Highlight = IF (ISFILTERED(usp_EM_Code) && VALUES(usp_EM_Code_Dup[NoteResponsibleProvName]) IN VALUES (usp_EM_Code[NoteResponsibleProvName]), usp_EM_Code_Dup[CPT_Code], "XX" & usp_EM_Code_Dup[CPT_Code])

 

I have also tired 

 

Provider_Highlight =

var Prov_Name = SELECTEDVALUE(usp_EM_Code[NoteResponsibleProvName])

var _True = SWITCH(TRUE(),

[CPT_Code] = “99282”, “#C8C8C8”,

[CPT_Code] = “99283”, “#3D3D3D”,

[CPT_Code] = “99284”, “#E1C4AC”,

[CPT_Code] = “99285”, “#007360”,

[CPT_Code] = “99291”, “#A51417”)


var _False = SWITCH(TRUE(),

[CPT_Code] = “99282”, “#EB895F”,

[CPT_Code] = “99283”, “#A666B0”,

[CPT_Code] = “99284”, “#E1C233”,

[CPT_Code] = “99285”, “#41A4FF”,

[CPT_Code] = “99291”, “#E669B9”)

 

RETURN IF(Prov_Name in VALUES (usp_EM_Code_Dup[NoteResponsibleProvName]), _True, _False)

 

I am new to DAX and out of ideas! 

 

-AO

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi AO90 ,

    According to your description, one possible solution is to use the Interactions feature in Power BI Desktop. This feature allows you to control how visuals on a report page affect each other. You can set the slicer to highlight the stacked bar chart instead of filtering it. To do this, you need to follow these steps:

    • Select the slicer on the report canvas.
    • Go to the Format tab on the ribbon and click Edit interactions.
    • You will see two icons on the upper-right corner of the stacked bar chart: a filter icon and a highlight icon. Click the highlight icon to enable highlighting for the slicer.
    • Click anywhere on the report canvas to exit the Edit interactions mode.

    Now, when you select a provider in the slicer, the corresponding bar in the stacked bar chart will be highlighted, while the rest will be dimmed. You can also select multiple providers by holding Ctrl or Shift while clicking on the slicer values.

     

    Another possible solution is to use conditional formatting for the colors of the stacked bar chart. This feature allows you to change the colors of the bars based on a measure or a field. You can create a measure that returns different colors depending on whether the provider is selected in the slicer or not. To do this, you need to create a new measure in your data model using DAX. You can use your existing Provider_Highlight measure or modify it as needed. For example, you can use this code:

    Provider_Highlight =
VAR Prov_Name =
    SELECTEDVALUE ( usp_EM_Code[NoteResponsibleProvName] )
VAR _True =
    SWITCH (
        TRUE (),
        [CPT_Code] = "99282", "#C8C8C8",
        [CPT_Code] = "99283", "#3D3D3D",
        [CPT_Code] = "99284", "#E1C4AC",
        [CPT_Code] = "99285", "#007360",
        [CPT_Code] = "99291", "#A51417"
    )
VAR _False =
    SWITCH (
        TRUE (),
        [CPT_Code] = "99282", "#EB895F",
        [CPT_Code] = "99283", "#A666B0",
        [CPT_Code] = "99284", "#E1C233",
        [CPT_Code] = "99285", "#41A4FF",
        [CPT_Code] = "99291", "#E669B9"
    )
RETURN
    IF (
        Prov_Name IN VALUES ( usp_EM_Code_Dup[NoteResponsibleProvName] ),
        _True,
        _False
    )
​

     

    Now, when you select a provider in the slicer, the corresponding bar in the stacked bar chart will have a different color than the rest.

     

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi AO90 ,

    According to your description, one possible solution is to use the Interactions feature in Power BI Desktop. This feature allows you to control how visuals on a report page affect each other. You can set the slicer to highlight the stacked bar chart instead of filtering it. To do this, you need to follow these steps:

    • Select the slicer on the report canvas.
    • Go to the Format tab on the ribbon and click Edit interactions.
    • You will see two icons on the upper-right corner of the stacked bar chart: a filter icon and a highlight icon. Click the highlight icon to enable highlighting for the slicer.
    • Click anywhere on the report canvas to exit the Edit interactions mode.

    Now, when you select a provider in the slicer, the corresponding bar in the stacked bar chart will be highlighted, while the rest will be dimmed. You can also select multiple providers by holding Ctrl or Shift while clicking on the slicer values.

     

    Another possible solution is to use conditional formatting for the colors of the stacked bar chart. This feature allows you to change the colors of the bars based on a measure or a field. You can create a measure that returns different colors depending on whether the provider is selected in the slicer or not. To do this, you need to create a new measure in your data model using DAX. You can use your existing Provider_Highlight measure or modify it as needed. For example, you can use this code:

    Provider_Highlight =
VAR Prov_Name =
    SELECTEDVALUE ( usp_EM_Code[NoteResponsibleProvName] )
VAR _True =
    SWITCH (
        TRUE (),
        [CPT_Code] = "99282", "#C8C8C8",
        [CPT_Code] = "99283", "#3D3D3D",
        [CPT_Code] = "99284", "#E1C4AC",
        [CPT_Code] = "99285", "#007360",
        [CPT_Code] = "99291", "#A51417"
    )
VAR _False =
    SWITCH (
        TRUE (),
        [CPT_Code] = "99282", "#EB895F",
        [CPT_Code] = "99283", "#A666B0",
        [CPT_Code] = "99284", "#E1C233",
        [CPT_Code] = "99285", "#41A4FF",
        [CPT_Code] = "99291", "#E669B9"
    )
RETURN
    IF (
        Prov_Name IN VALUES ( usp_EM_Code_Dup[NoteResponsibleProvName] ),
        _True,
        _False
    )
​

     

    Now, when you select a provider in the slicer, the corresponding bar in the stacked bar chart will have a different color than the rest.

     

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.