cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paulien1989 Regular Visitor
Regular Visitor

Graph with date shows months not selected through filter

Hi everyone,

 

I'm currently struggling with visualizing data in a line graph where at some points, there is no data.

 

Speficially: I want to show the number of emails sent over time. On the y axis I have the total numbers of emails sent and on the x asis a date hierarchy. The number of emails sent is a measure:

 

# emails sent = CALCULATE(DISTINCTCOUNT('Email Result'[Email Send ID])+0)

 

The +0 was a trick I found on this forum, to show zero's instead of blanks where the count is 0 (or non existent). Without it, the line graph would just not show the months at all without data (the x axis would go from july to october, skipping august and september...).

 

The date is filtered on the last 12 calendar months.

 

Now, the graph looks like this:

 

linegraph.PNG

It is still showing the months not belonging in the filtered date range! I tried all sorts of work arounds, but it keeps giving me the months I do NOT want to show. Anyone have a answer?

 

4 REPLIES 4
Super User
Super User

Re: Graph with date shows months not selected through filter

How are you doing the filtering to the last 12 months?

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Paulien1989 Regular Visitor
Regular Visitor

Re: Graph with date shows months not selected through filter

I tried several filters:

 

- Visual relative filter Last 12 Months (calendar)

- Page relative filter Lat 12 Months (calendar)

- Date slicer selecting 1st may 2017 thru 30th april 2018

- Page / visual filter stating Date is on or after 1st may 2017 AND before 1st may 2018

 

All give this same result...

 

I now have a workaround where I used a Year-Month text field on the axis and I manually selected the months I want to show, sorting them by a year-month numeric index dragged into Tooltips. It's not dynamic though.... cannot use dynamic date filters this way.

 

Hoping for a correct and dynamic solution.

Microsoft v-yulgu-msft
Microsoft

Re: Graph with date shows months not selected through filter

Hi @Paulien1989,

 

Is there an existing relationship between calendar table and fact table based on date column? If so, you just need to add [date] from calendar to slicer, also add it to X-axis of chart. When you select to show Last 12 months from relative slicer, the chart will be fitered dynamically.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MalJan19 Frequent Visitor
Frequent Visitor

Re: Graph with date shows months not selected through filter

I was having the same problem, and I solved the issue by marking the table joined to my date as the date table. It works perfectly now!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)