Line chart not showing data point as 0 when measure = null

[elambs]

I am trying to create a line chart showing the number of rows that include CNF but not TECO using the following measure as the y axis:

CNF no TECO = (CALCULATE(COUNTROWS(Table), (FILTER(Table, SEARCH("TECO",[Column],,0)=0))))

The x-axis shows the date, all the rows are associated to various dates but I am trying to graph the data as a weekly report so I created a column within my table that I connected to a separate data table using the following measure:

Week Start Date = 'Costs'[Actual Finish] - WEEKDAY('Costs'[Actual Finish],2) + 1

 

However, for certain weeks there will be 0 CNF with no TECO rows and that is not being reflected on my table (considering that this data ranges from Dec 11 2023 to Jan 15 2023):

I have tried adding +0 at the end of the measure for my y-axis but that then gives me a chart with daily data points instead of weekly:

I would like my line chart to show my data points week by week and not day by day as shown above, while still plotting data points as zero when the result of the measure is null. I have tried the "Show items with no data" option with the x-axis with no success. What am I missing? Am I using the wrong measures? 

[Anonymous]

Hi, @elambs 

You can try creating a separate table that contains all the weeks in the date range you're interested in, and then use the measure to calculate the number of rows for each week in the original table and meet the criteria for including CNF but not TECO. This way, even if there are no rows in the original table that match the criteria for a given week, the corresponding data point in the chart will still be plotted as 0.

Maybe you will be able to provide some sample data or PBIX files. And don't give away your private information.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[123abc · Accepted solution]

To ensure that your line chart displays data points for weeks with zero values for CNF no TECO, you can adjust your DAX measure and your data model. Here's how you can do it:

1. Adjust the DAX Measure: You need to ensure that your measure returns zero instead of null when there are no CNF rows without TECO. You can achieve this by using the IF function. Here's how you can adjust your measure:

CNF no TECO =
VAR CNF_No_TECO_Count = CALCULATE(COUNTROWS(Table), FILTER(Table, SEARCH("TECO",[Column],,0)=0))
RETURN
IF(ISBLANK(CNF_No_TECO_Count), 0, CNF_No_TECO_Count)

1. This measure checks if the count of CNF rows without TECO is blank. If it is blank, it returns 0; otherwise, it returns the count.

2. Ensure Proper Data Model Relationship: Ensure that your date table is properly related to your data table. Make sure you have a date column in your date table that represents the week start date (Actual Finish - WEEKDAY(Actual Finish, 2) + 1), and it should be related to the date column in your data table.

3. Set Up the Line Chart: Once you've adjusted your measure and ensured proper relationships in your data model, create a line chart with the following settings:

   • X-axis: Use the week start date from your date table.
   • Y-axis: Use the CNF no TECO measure you just created.

With these adjustments, your line chart should now display data points for weeks with zero values for CNF no TECO, and it should plot data points on a weekly basis.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[elambs]

This worked great! Thank you so much for your reply