Show blank as 0 in graph of Measure while applying Date filter to graph axis

[Anonymous]

Hi all,

I am running into an issue with a dashboard I created with graphs that show different Support Ticket metrics trends. Currently, the graphs are plotted using Measures that count Opened, Closed, Open issues based on a Date table, however an issue has been raised that when there are 0 issues raised on certain dates, there is a gap in the graph rather than the value 0.

Example measure (Combined_issues = main table holding all support tickets + details; Combined_issues_trend = Date table generated in PowerQuery with all Measures on trends):

Opened issues =

VAR tmpTickets = ADDCOLUMNS(Combined_issues,"Effective Date",IF(ISBLANK([dl_resolved]),"2100-01-01",[dl_resolved]))

VAR tmpTable =

SELECTCOLUMNS(

FILTER(

GENERATE(

tmpTickets,

'Combined_issues_trend'

),

[Date] = [dl_created].[Date]

),

"ID",[dl_key],

"Date",[Date].[Date]

)

VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

I have done some research, and tried to add a +0 on the end of my COUNTROWS() which solves the original problem and forces the value down to 0 on the axis when there are no issues. However, this causes the date slicer on the page to break. Specifically, the axis then starts at the beginning of the Date table (2015), and slicing by the Date field only forces all of the data to 0 on the axis up until the date slicer starts - see screenshot below:

[Anonymous]

Thank you @Greg_Deckler 

Apparently screenshots not available...

[Greg_Deckler]

@Anonymous - All I can think of is doing this. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Now, you would need to build this into your measusre where you would add your final calculation to a summarized table within the measure. Then you would check if all rows less than the current row (max of your date within context) are 0 and if so, return blank. This would eliminate everything off of the front end but preserve the 0's in the middle of your data.

[Greg_Deckler]

@Anonymous Not sure about the date slicer, can't see screen shot.