COALESCE issue in Power BI

[AshwinP]

We have Scenario where the Power BI report contains 3 calculated measures (a combination of dimension and fact columns) and 4 attribute columns from 4 different dimension tables.

The report is created based on those 4 attribute columns and 3 measures. The slicers are created based on these dim attribute columns. Since there are some null values in the measures, we tried replacing them with zeros using COALESCE function.

Now, the resulting report is bringing all the data from the fact irrespective of the slicer selection.

For example:

Let’s consider we have “DIM_DAY” as a Dimension table, “REVENUE_FACT” as a fact table. When calendar date is applied for 2024 as a slicer, the report should show the data for 2024. This is the case when null values are not converted to zeros. However when the COALESCE is applied on the measure, all the calendar dates are available in the DIM_DAY is getting displayed. The measure value is displayed as for all the calendar dates other than 2024. As 2024 is selected in the slicer we are getting the correct value of the measure.

The grand total remains the same before and after replacing nulls with zeros, the number of rows has increased significantly due to these extra zero rows.

[v-venuppu]

Hi @AshwinP ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu · Accepted solution]

Hi @AshwinP ,

I have tried replicating the scenario in the same PBIX file shared by you.Please go through the attached PBIX file for your reference.Hope this helps..!!

Thank you.

[Poojara_D12]

Hi @AshwinP 

You're facing an issue in your Power BI report where replacing null values in your measures with zeros using the COALESCE function is unintentionally altering the report’s filtering behavior. Initially, when the measure contains null values, the report correctly respects slicer selections (like filtering DIM_DAY[Calendar Date] to 2024), and only relevant data appears. However, after applying COALESCE([Measure], 0), the report starts returning rows for all dates from the DIM_DAY table, not just those related to filtered fact data. This happens because when nulls are left untouched, rows without matching data in the fact table are naturally excluded from visuals (as their measure returns blank). But when you use COALESCE, you’re effectively forcing those unmatched dimension rows (which originally had no fact data) to be included with a value of zero, making them visible in the visuals. As a result, your report now includes extra rows from the dimension table with zero values, inflating the row count and cluttering the view, even though the total value remains accurate. To fix this, you can wrap the measure logic inside a HASONEVALUE or use IF(HASONEVALUE(DIM_DAY[Calendar Date]), COALESCE([Measure], 0), BLANK()) to suppress display of irrelevant zero rows based on context, or explicitly filter out unrelated rows using ISFILTERED or ISINSCOPE combined with FILTER() logic in your DAX to ensure only meaningful combinations appear.

[AshwinP]

Hi @Poojara_D12  We tried the approach suggested but did not get the desired results. The updated pbix is available here - Power-BI/COALESCE issue in Power BI.pbix at main · varunaluri18/Power-BI · GitHub Requesting your inputs.

[v-venuppu]

Hi @AshwinP ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[AshwinP]

 https://github.com/varunaluri18/Power-BI/blob/main/COALESCE%20issue%20in%20Power%20BI.pbix 

@v-venuppu @lbendlin Please find the sample data in the above link. Request your inputs. 

Thanks

[v-venuppu]

Hi @AshwinP ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

Can you please share the sample data and output as suggested by Ibendlin, so that it will be helpful for us to solve the issue.

Thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...