Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Graph the incident count over a time range by turning blank data to 0

Hello everyone

I'm having trouble with the date filter (showing a date range) when it comes to graphing the count of the occurrences of some errors by months.

 

The problem lies in a pecualiarity of my data set:

This pecualirado is that there are months in which the errors may not have occurred, in these cases Power Bi ignores this data and puts them as a BLANK. Graphing the data as follows:

 

The problem is that when there are intermediate months without data I want it to appear as 0, to solve this what I have done is create the following measure, which solves the problem:

Count of code with 0 = COUNT('Red Dawns Alarms'[CODE]) + 0

But if I plot that measure and apply the date range filter, instead of not showing the values that are outside the date range in the graph, what it does is convert those values to 0. I've been trying a lot of things, but I'm not able to avoid this interaction.

As you can see in the image what DAX does is put the Count(code) = BLANK to make it disappear but my measurement converts it as 0.

Something that had also occurred to me was to create a table calculated as follows:

Known = ADDMISSINGITEMS(SUMMARIZECOLUMNS('Red Dawns Alarms'[CODE],'Date Table'[Date].[MonthNo],'Date Table'[Date].[Year], "Incidents", 0+COUNT('Red Dawns Alarms'[CODE])))
In this way I have a column directly with the data of the count and in this way I thought I could graph it but instead of graphing the value of the column incidents, graph the value with an operation of the type: max, min, sum, average ... so it doesn't show me what I want.
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I updated your sample pbix file(see the attachment), please check if that is what you want. You can delete the relationship between the table 'Conocido' and 'Date Table'.

    Best Regards

  • Hello, I have already solved it. The creation of the table I had mentioned is enough. The Sum of incidents of the known table shows the desired result and manages to make the values disappear outside the date range that is determined through the filter.

3 Replies