Forum Discussion

Rosenshredder's avatar
Rosenshredder
Helper I
4 years ago
Solved

Line chart not showing zeros when distinct count is zero

I've seen numerous posts about this, but I haven't been able to get any of those solutions to work for me. I've got a line chart with a continuous x-axis showing the previous week. The value is the d...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Rosenshredder ,

     

    You will need to create a calendar table and use the calendar[date] as axis.
    Then create a measure like below:

    CountDriver =
CALCULATE (
    DISTINCTCOUNT ( 'Test Data File for Power BI'[Driver] ),
    FILTER (
        ALLSELECTED ( 'Test Data File for Power BI' ),
        'Test Data File for Power BI'[date] = SELECTEDVALUE ( 'calendar'[date] )
    )
) + 0

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Rosenshredder ,

 

You will need to create a calendar table and use the calendar[date] as axis.
Then create a measure like below:

CountDriver =
CALCULATE (
    DISTINCTCOUNT ( 'Test Data File for Power BI'[Driver] ),
    FILTER (
        ALLSELECTED ( 'Test Data File for Power BI' ),
        'Test Data File for Power BI'[date] = SELECTEDVALUE ( 'calendar'[date] )
    )
) + 0

 

Best Regards,

Jay

Rosenshredder's avatar
Rosenshredder
Helper I
4 years ago

Jay, I'm sure this is the correct answer, so thank you for that! However, it brings up another question for this newbie: How should I create a calendar table in this scenario? Should I create another table in the excel file and import it as well? Or should I create the table within Power BI (never done this before)? 
And, I imagine the table would only need to have one column of all the dates in the range, right? There is a column in the main table with dates as well, so I could create the relationship between them off just that single column, I would think.