Line Chart: Filter out legend item when total = 0, but keep 0s in for legend items when total >0

[AbigailCole]

TLDR:

I need to make a line graph that filters out the legend if the record's total is 0, but I still want 0s to show for records that don't total to 0. I assume it will need a virtual table, but I am new to this subject and struggling to piece it together.

Okay, so I have two tables that I need to count the rows in, so I have this measure:

Count =

VAR table1Count = DISTINCTCOUNT('table1'[ID])

VAR table2Count = DISTINCTCOUNT('table2'[ID])

RETURN table1Count + table2Count + 0

I also have a bridge table from the two above tables of distinct defect details so the users can slice and dice the data using it.

Key Defect Details =

DISTINCT(UNION(DISTINCT('table1'[DEFECT_DETAIL]), DISTINCT('table2'[DEFECT_DETAILS])))

My goal is to create a line graph that shows the count over time using defect details as the legend, but still keep a continous line even if it goes to 0. AKA, I want to see the line of the defect detail go to 0 and keep all the months showing up on the x-axis even if there were no defect details for that month, but if it never leaves the 0 line, I don't want to see that defect detail at all.. However, I have this issue since I'm using a bridge table (I assume) that when I added the +0 to my count measure, it will show a defect detail even if it's 0 all the way across the graph. (Example:

There is no Art Work at all for the time period selected, but it still shows up in this graph. It also isn't showing other higher defect details like Missing Print has 9 in March 2025, but I assume that's due to 375 defect details trying to be in a legend on this graph lol...)

When I filter out when Count > 0, I lose my continous lines across the graph and the x-axis skips months without defect details (note Feb 2025 is missing below), but the graph becomes accurate again at least..

Essentially, I'm trying to graph this table WITH the 0s:

But instead it keeps graphing this table:

And when I use the filter panel to show items when the value is greater than 0, it takes out the 0s of the top graph.. So how do I essentially filter out defect details if the total is 0 over the whole graph but keep the 0s for the defect details that have more than 1 count in the graph?

I've tried quite a few different calculate and fitler combos with all and all selected, everything has the same effect 😞

Thanks,

Abbey

[GeraldGEmerick]

@AbigailCole Sample data would be really helpful here.

[AbigailCole]

Sure! Here is a snipet of my dataset: https://docs.google.com/spreadsheets/d/1PivKeHkDNb_wHpgjr0oPf2u46jIQzk9w3LwfcOVIOzY/edit?usp=sharing

The goal is when I use a slicer to only show the Defect Type of Print Quality (also a bridge table), it will show this graph:

but instead, it is showing up like this:

Since I'm using two bridge tables (one for Defect Type and one for Defect Details), it doesn't know that Not Sealed and Paper Wrinkles don't have anything to do with Print Quality. Sorry, I guess I forgot to mention the second bridge table in the original post...

But that's why using the filter panel to only show records with a Count greater than 0 works to filter out the Defect Details that are not actually associated for this Defect Type, but I am looking for a way to keep the line graph showing the 0s for those that have at least one record in the time frame (aka still showing their 0s and not being scattered dots - my boss doesn't like it without the lines.)

Thank you so much for your help! 

[v-dineshya]

Hi @AbigailCole ,

Thank you for reaching out to the Microsoft Community Forum.

I have raised access request to access the dataset, that you have provided. Could you please approve the access request to dataset?, to work on the solution.

Regards,

Dinesh