Forum Discussion
Excel Fabric / Power BI - Distinct Count Not Working/Selectable
Hi pk00 ,
Thank you for engaging with the Microsoft Fabric Community. This looks like a known limitation when using Analyze in Excel or live Excel PivotTables with Power BI / Fabric datasets.
When Excel connects to a Power BI/Fabric dataset, it works against the semantic model. In this setup, Excel can only use measures that already exist in the model it can’t create certain aggregations (such as Distinct Count) on its own.
Microsoft mentions this behavior in their Analyze in Excel documentation: Troubleshoot the connection from Excel to Power BI data - Power BI | Microsoft Learn noting that calculations are handled by the model rather than by Excel.
Because of this, the Distinct Count option may be disabled or may not return values unless a corresponding DISTINCTCOUNT DAX measure is defined in the dataset.
Other helpful Document:
DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn
Create Excel Workbooks with Refreshable Power BI Data - Power BI | Microsoft Learn
I hope this clarifies the situation. If I’ve misunderstood any part of your situation, please let us know.
Thank you for the prompt reply. I have been using the distinct count functionality successfully in live data connections Excel pivots for many years now. In fact, distinct count still works with live pivots set up prior to a few days ago. Something has changed and it is no longer working when creating a new live connection, initiated from Power BI Analyze in Excel or Get Data Fabric Excel connection methods.
This seems like a bug, can it be fixed? As stated, distinct count currently works with live data connections I've set up as recently as last week and continues to work. But when setting up new PBI/Fabric live data connections, the UI in the pivot value field settings looks different from before and distinct count isn't selectable. It doesn't seem like a legitimate limitation when this worked in the past and continues to work for previously created live pivots.
Please advise. Thank you.
Hi pk00 ,
Thank you for your patience. I’ve validated this behavior internally by creating new Excel live connections to Power BI/Fabric datasets and can confirm that Distinct Count is no longer available for newly created PivotTables, while existing PivotTables continue to function as expected, as you mentioned.
FYI:
This confirms the issue is a recent regression in the current experience. To have this investigated further, please raise a Microsoft Support ticket, as the support team will be able to engage the product team and provide updates or resolution details.
You can raise a support ticket using the following link:
Create a Fabric and Power BI Support Ticket - Power BI | Microsoft LearnThank You.