Need to chart blank as 0 on a Line Graph AND obey Date Slicer

[jcolby]

Greetings,

Relatively new to PowerBI.  I've been stuck on this issue for a while.  Checked around on here and found 50% of the solution I need.

I need to chart blank values as 0 on a Line Graph

I was able to do so using this measure:

Count of Ticket # = 
IF (
    ISBLANK(DISTINCTCOUNT('Ticket Statistics'[Ticket #])),
    0,
    DISTINCTCOUNT('Ticket Statistics'[Ticket #])
)

But now it shows 0 for dimensions outside of the slicer range, how do I still only show whats inside my selected range?

What do?

Thanks

[jcolby]

@Ashish_Mathur 

Excellent!

Thank you very much

So in addition to needing the calendar table(have on my model, not on sample), I also needed the order column + month/order table to make it all work.

Is the month order table & its relation to the calendar table like that pretty standard?  Thanks again

[Ashish_Mathur]

You are welcome.  Yes, it is pretty much a standard.

[jcolby]

Duly noted.

[parry2k]

@jcolby try this measure

 Count of Ticket # = DISTINCTCOUNT('Ticket Statistics'[Ticket #]) + 0

[jcolby]

@parry2k

Thanks for the reply!

Gave that measure a try but same results, still shows the 0s outside of the selected range

[parry2k]

@jcolby do you have date dimension which you using for date slicer? How your tables are related? Seems like there is something else going on?

[jcolby]

@parry2k I have only one table.  It contains a "Date Opened" field.  This is what I used on the slicer.

Do I need to create a month table + relationship and use that on the slicer?  Seen some things along those lines but didnt fully understand.

[parry2k]

@jcolby yeah as a best practice, add date dimension in your model ( there are many posts on how to add date dimension) and then set relationship between date table and your transaction table, and use date from date dimension for slicer.

[jcolby]

@parry2k Hey again,

Had some more time to work at this.  I've now added a date dimension table using:

Calendar = CALENDARAUTO()

And have setup a many:1 relationship from my original date opened column.

I added this to the slicer

It all works fine, except - my original issue is unchanged.  I still see the 0's outside of the selected range on the slicer.

And after working through this, I don't need any date dimensions that I didnt already have (Year & Month only), so though you mentioned its best practice to have a date dimension table, i dont believe it changes anything in my case

Is there a way to specify the measure should only touch rows that are inside my slicer range or something like that?

So close but cant get over this hump

[Ashish_Mathur]

Hi,

This should be possible.  Share the link from where i can download your PBI file.

[jcolby]

@Ashish_Mathur 

Hey,

Thanks for looking, much appreciated

https://www.dropbox.com/s/oi23wpxs2h7942w/chart0-sample.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

I do not see any problem at all.  Look at the Duplicate of page1 tab.  Download the PBI file from here.

Hope this helps.