Forum Discussion

Paulien1989's avatar
Paulien1989
Icon for Advocate II rankAdvocate II
8 years ago

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:

 

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?

 

Need Help

5 Replies

    • Paulien1989's avatar
      Paulien1989
      Icon for Advocate II rankAdvocate II
      8 years ago

      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.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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

  • MalJan19's avatar
    MalJan19
    Frequent Visitor
    7 years ago

    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!