Power BI: unique value (no duplicate) with distinctcount not showing the chart correctly

In Power BI, to not count duplicate values, I will use the distinctcount function allowing me to show only unique values but it may happen that the result is not displaying correctly, showing more than expected.

Imagine that I have this data:

power bi

I will create a column chart and I will put the month and the incident:

power bi

As we can see, it counts the duplicate in January so to avoid it, I will ask it to count distinct:

power bi

In January, it shows only 1 instead of 2, so this is correct but from October, it shows 1 and in my data, I have nothing for those last 3 months.

power bi

The reason for that, it is that the distinct count function is also counting the empty cells. To avoid it, I will have to create a measure by using the DISTINCTCOUNTNOBLANK function.

power bi

Then in the “Y-axis”, I will replace the “incident” by the “measure” and my chart will display correctly.

power bi
power bi

Interesting Topics