Dynamic Legend and Line Chart Comparison

[0Experience]

Hi everyone,

I’m working on a Power BI report and facing a few challenges I’d love your input on.

I have data spanning from 2018 to 2023, with three types for each year: Type-1, Type-2, and Type-3. I’ve created a new combined type called Type-12, which merges Type-1 and Type-2. Each type also has data for five different locations.

I want to build a column chart that dynamically updates based on a slicer selection:

• If I select Type-1, the chart should show only Type-1 values.
• If I select Type-12, it should display a stacked column combining Type-1 and Type-2.
• The chart uses separate measures for each type.

The issue:

Even when I select only Type-1 or Type-12, the legend still shows all measures (Type-1, Type-2, Type-3, Type-12), because they’re used in the Y-axis. I want the legend to reflect only the selected types from the slicer.

Also, I’m using both Year and Type on the X-axis, which is necessary for my layout, but it’s making the legend labels too long and cluttered (see screenshot). Is there a way to simplify or hide part of the legend without losing the dual-axis structure?

Line chart:

I also have a line chart comparing Type-1 and Type-12. Since each type has 3 location points per year, I end up with 6 points per year (3 for each type), which makes sense. But visually, it’s hard to compare the two types clearly. Is there a better way to structure this line chart for easier comparison?

I have attached power bi file , and any suggestions or workarounds would be greatly appreciated!

[v-dineshya]

Hi @0Experience ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide expected output snap.  It will easy for us to replicate the issue.

Regards,

Dinesh

[Greg_Deckler]

@0Experience Create a relationship between your two tables. Then use a standard count measure, just:

Measure = COUNTROWS( 'Test_Data_Table' ) and put that in your Y-axis instead of all of your other measures. Put type column in your Legend. If necessary, you can account for type 12 in your single measure using an IF statement like:

Measure =
    IF (
        "type-12" IN VALUES('type_sort'[type]),
            CALCULATE(
                COUNTROWS(Test_Data_Table),
                FILTER( ALL( Test_Data_Table ), Test_Data_Table[type] = "type-1")
            ),
            COUNTROWS( Test_Data_Table )
    )

[0Experience]

Hello @Greg_Deckler 

Thanks for your prompt reply and your suggestions. But this will not help me. As I need stacked column for type 1 and type 2 (not a single column). See the screenshot please

 

Even if I put my type column in legend it is not fulfiling my purpose.