Issue with Filtering and Missing Closed Sales Value in Power BI

[DeepthiS]

Hello,

I'm working with a Power BI report that contains data from multiple tables, and I’ve encountered an issue when filtering by Opportunity ID and Selected Quarter. Specifically, when I filter by Opportunity ID, the Created Date column works fine, but the Closed Sales Value does not appear, even though I know the data exists in the backend. Here’s the setup:

Setup:

• I have Opportunity ID, Sales, Created Date, and Closed Date columns coming from a single fact table (let's call it FactSales).
• I also have a Selected Quarter slicer, which is connected to a separate Date table (let's call it DateTable).
• There are additional slicers for filtering by Opportunity ID and Sales segments on the report page.

The issue:

• When I apply a filter on the Opportunity ID, the data appears as expected for some of the Opportunity IDs, including the Created Date.
• However, for certain Opportunity IDs, the Closed Sales Value does not show, even though there is data for the Closed Date and Sales in the backend.
• The filter on Selected Quarter is working correctly, and data is displayed for the selected quarter for those Opportunity IDs where the closed sales value is missing.

Example:

• Opportunity ID 12345: Shows Created Date and Closed Sales Value.
• Opportunity ID 67890: Shows Created Date, but Closed Sales Value is missing.

I’ve checked the relationships between the tables and confirmed that the Created Date and Closed Date are correctly related to the DateTable. The missing data seems to be specific to certain Opportunity IDs, even though the backend data contains the corresponding closed sales values.

What I’ve tried:

• I’ve verified that the Closed Sales Value data exists for the affected Opportunity IDs in the backend.
• I've checked the relationship between the FactSales table and the DateTable to ensure it’s correct (one-to-many relationship, with FactSales.Created Date and FactSales.Closed Date both connected to DateTable).
• The missing sales values don't seem to be caused by any row-level security or filters directly applied to the Closed Sales Value.

Can anyone help me figure out why the Closed Sales Value is missing when filtering by Opportunity ID for certain cases, despite data being present for the affected Opportunity IDs? Any suggestions on what might be going wrong would be greatly appreciated!

Thank you!

[DeepthiS]

Thanks or your reply, I’m encountering an issue in Power BI where Geo, Region, and Country columns are working fine when added to a table alongside Created Date value and Closed Sales Value (i.e., the values appear correctly for the selected Opportunity ID). However, when I add the same columns to slicers, they return blank values instead of showing the expected options.

[Anonymous]

Hi @DeepthiS ,

Is it possible to provide .pbix files without sensitive data? Or provide some sample data?

If you are unsure how to upload data please refer to

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards

[DataNinja777]

Hi @DeepthiS ,

The issue of missing Closed Sales Value for certain Opportunity IDs in Power BI, despite the data being present in the backend, is likely caused by a combination of filter context conflicts, relationship ambiguity, or data model limitations. Here's how to address these possibilities:

First, check if the Selected Quarter slicer is unintentionally excluding the Closed Sales Value data. This can happen if the Closed Date for the missing Opportunity IDs falls outside the selected quarter. To confirm, verify that the Closed Date aligns with the current selection in the slicer.

Another potential issue is the handling of relationships between the FactSales table and the DateTable. If both Created Date and Closed Date are connected to the DateTable, Power BI might not apply filters as expected due to relationship ambiguity. This can be resolved by using a role-playing table approach—create separate date tables for Created Date and Closed Date and connect them to the respective columns in the FactSales table.

Alternatively, if the relationship between FactSales.Closed Date and DateTable is inactive, the Closed Sales Value data won't automatically appear unless explicitly activated. You can use the USERELATIONSHIP function in a DAX measure to ensure the correct relationship is applied. For example:

Closed Sales Value = 
    CALCULATE(
        SUM(FactSales[Sales]),
        USERELATIONSHIP(FactSales[Closed Date], DateTable[Date]),
        ALLSELECTED(DateTable)
    )

This formula ensures that the Closed Date relationship is active for the calculation while respecting the slicer selections from the DateTable.

If the issue persists, inspect the cross-filter direction between FactSales and DateTable. The direction should be set to "Both" to allow filters to flow correctly. If the cross-filter direction is not suitable, modify it in the relationship settings or adjust your DAX measures to handle the filtering explicitly.

In some cases, the problem may lie in the data itself. Validate that the Closed Date and Sales columns in the FactSales table are complete and correctly populated for the affected Opportunity IDs. Null or mismatched values in these columns could prevent the data from appearing in the visual.

To debug further, create a table visual that includes Opportunity ID, Created Date, Closed Date, and Closed Sales Value. This will help identify patterns in the missing data and verify if slicers or filters are interfering with the results. Additionally, test the calculation without slicers using a simplified DAX measure to isolate the issue:

Test Measure = 
    CALCULATE(
        SUM(FactSales[Sales]),
        USERELATIONSHIP(FactSales[Closed Date], DateTable[Date])
    )

By systematically testing these aspects, including relationships, filter context, and data integrity, you should be able to identify and resolve the issue with the missing Closed Sales Value. If further clarification or specific troubleshooting is needed, feel free to ask.

Best regards,