to show some date column as zero when there is no date available

[Lingeshwaran]

 I have two tables which consists of some metric's name in the x-axis and the y axis consisting of count of dates for the visualization for the  clustered column chart. Actually, the second column consists of a list of dates. In some metrices, there are no dates available such that I have to show them as zero as counts especially and I also need to create  a tiny little graph for the visualization of the zero counts with the other metrices which range above than the zero counts. Help me with this in Powerbi

[v-pnaroju-msft]

Hi Lingeshwaran,

We are following up to check if your query has been resolved. If you have found a solution, kindly share it with the community to help others facing similar issues.

If our response was helpful, please mark it as the accepted solution. This will benefit the wider community.

Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi Lingeshwaran,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi Lingeshwaran,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @bhanu_gautam, @Akash_Varuna , @danextian for your response.

Hi Lingeshwaran,

We would like to check if the solution provided by @bhanu_gautam , @Akash_Varuna  and @danextian has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[danextian]

Hi @Lingeshwaran 

Make sure you have a separate dates/calendar table with a complese set of dates and relate that to your fact table in a one-to-many single direction relationship. You can create such a table in DAX using the CALENDAR function. Add to or subtract 0 from your existing measure. The new measure would be:

Value + 0 = [my measure] + 0

 Use the column from the dates table in your visual and not the one  from your fact as you can't assign a value to a row that doesn't exist - eg., your fact table doesn't have 1/1/25, it won't appear in the viz if you add zero to your measure.

[Akash_Varuna]

Hi @Lingeshwaran To show zero counts when no dates are there Could you please try these

1. Create a summary table with all metrics and count of dates:

   MetricsSummary = 
   ADDCOLUMNS(
       GENERATEALL(VALUES(Table1[MetricName])),
       "DateCount", COUNT(Table2[DateColumn])
   )

2. Handle missing dates by replacing blanks with zeros:

   DateCountWithZero = 
   IF(ISBLANK([DateCount]), 0, [DateCount])

3. Use the summary table for the clustered column chart with MetricName on X-axis and DateCountWithZero on Y-axis. Adjust the Y-axis to start at 0 for clear visualization.
   If this post helped please do give a kudos and accept this as a solution
   Thanks In Advance

[bhanu_gautam]

@Lingeshwaran Ensure you have a date table in your model. This table should cover the range of dates you are interested in.

Create a measure that counts the dates and returns zero if there are no dates:

DAX
DateCount =
VAR DateCountValue = COUNT('YourDataTable'[DateColumn])
RETURN
IF(ISBLANK(DateCountValue), 0, DateCountValue)

Add a clustered column chart to your report.
Drag the metric names to the x-axis.
Drag the DateCount measure to the y-axis.

To ensure that metrics with no dates are displayed as zero, you might need to create a relationship between your metrics table and the date table, and use the DateCount measure in your visualization.