Search code examples
chartspowerbislicers

PowerBI: Change chart type upon selecting an element in Slicer

So I have this line chart in PowerBI which has Weeknumber as its X-axis. My Slicer also has Weeknumber as its Field (Element). When I select one single week from the slicer, the line chart turns to dots for obvious reasons. Is there any way in PowerBI for this line chart to be converted into bar chart in the same area when I select any element in my slicer.

This is what I have

This is what I want

Solution

  • Let's say your table is named ChartData and has the values for the line are given by measures [Line1] and [Line2].

    Change your chart into a Line and Clustered Column Chart with [Line1] and [Line2] in the Line Values section and ChartData[FiscalWeek] in the shared axis section.

    Define new measures which are blank unless a single week is selected:

    Bar1 = IF(COUNTROWS(ALLSELECTED(ChartData[FiscalWeek])) = 1, [Line1], BLANK())
Bar2 = IF(COUNTROWS(ALLSELECTED(ChartData[FiscalWeek])) = 1, [Line2], BLANK())

    Put those measures in the Column Values section and you should be good to go.

    Note that this will still show the dots when you have the single week selected. If you don't want this, then define the line measures to only be non-blank when multiple weeks are selected and use those in place of [Line1] and [Line2]. E.g.

    LineMeasure1 = IF(COUNTROWS(ALLSELECTED(ChartData[FiscalWeek])) > 1, [Line1], BLANK())
LineMeasure2 = IF(COUNTROWS(ALLSELECTED(ChartData[FiscalWeek])) > 1, [Line2], BLANK())

    It should look like this with no week selected:

    Lines

    And like this when one week is selected:

    Bars