Forum Discussion

MyThumbsClick's avatar
MyThumbsClick
Helper II
2 years ago
Solved

Count by Month - Line Graph Not Showing Zero Values

Hi All

 

I have a Projects table - example below:

Project_IDGo_Live_Date

1

01/01/2024
212/01/2024
311/03/2024
401/04/2024

I have a line graph visulation with:

  • X-axis - Go_Live_Date (date hierarchy - Year, Month)
  • Y-axis - Count of Project_ID

This works fine and shows total projects per month except its not showing a zero value for Feb when there is no project with a Go_Live_Date. Do I need to create a seperate Count measure and use some clever DAX?

 

Thanks!

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try this approach.

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
    2. Create a relationship (Many to One and Single) from the Go_Live_Date column to the Date column of the Calendar Table
    3. to your visual, drag Year and Month name from the Calendar Table
    4. Write this measure

    Measure = coalesce(distinctcount(AllProjects[ID]),0)

    Hope this helps.

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Try this approach.

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
    2. Create a relationship (Many to One and Single) from the Go_Live_Date column to the Date column of the Calendar Table
    3. to your visual, drag Year and Month name from the Calendar Table
    4. Write this measure

    Measure = coalesce(distinctcount(AllProjects[ID]),0)

    Hope this helps.

    • MyThumbsClick's avatar
      MyThumbsClick
      Helper II
      2 years ago

      Thanks. With your instructions (and a bit of fiddling with the sort), the graph is visualizing as i want it. 

       

      Greatly appreciated! 

    • MyThumbsClick's avatar
      MyThumbsClick
      Helper II
      2 years ago

      Hi Ashish

      I do have 1 more query if thats ok? I would like to add a filter to the measure dependant on another column value. Something like this: 

      measure = CALCULATE(coalesce(distinctcount(AllProjects[id]),FILTER(AllProjects,[m_a] = "True"),0))

       

      However, im getting the following error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

      Is there a way to achieve this?

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        Does this measure work?

        Measure = coalesce(calculate(distinctcount(AllProjects[id]),AllProjects[m_a] = "True"),0)

        If TRUE is a boolean value, then remove the double quotes.

        Hope this helps.

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    2 years ago

    Hello MyThumbsClick ,

     

    Yes you have to create separate measure for the count and after creating measure at the end of the measure please mention '+0' so that if the value is blank it will show '0'

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

    • MyThumbsClick's avatar
      MyThumbsClick
      Helper II
      2 years ago

      Thanks Kishore_KVN

       

      I created the below basic measure which does the trick and shows a zero value for where there is no count.
      projects_per_month_count = DISTINCTCOUNT(AllProjects[id])+0

       
      The problem im having is that i want to display the graph with a relative date filter - ''Is in the next 12 months". So Jan - March should be for months in 2025 and should appear after December but they are showing as if in normal calendar year. I have a feeling i cant achieve what im after with just a measure?