Forum Discussion
Count by Month - Line Graph Not Showing Zero Values
Hi,
Try this approach.
- Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
- Create a relationship (Many to One and Single) from the Go_Live_Date column to the Date column of the Calendar Table
- to your visual, drag Year and Month name from the Calendar Table
- Write this measure
Measure = coalesce(distinctcount(AllProjects[ID]),0)
Hope this helps.
Hi,
Try this approach.
- Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
- Create a relationship (Many to One and Single) from the Go_Live_Date column to the Date column of the Calendar Table
- to your visual, drag Year and Month name from the Calendar Table
- Write this measure
Measure = coalesce(distinctcount(AllProjects[ID]),0)
Hope this helps.
Thanks. With your instructions (and a bit of fiddling with the sort), the graph is visualizing as i want it.
Greatly appreciated!
You are welcome.
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
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.
Thats worked perfectly. Thank you
