Forum Discussion
Count counting some items twice
HI Justas4478,
Thank you for the clarification and work in identifying the root cause.
You are right, the inflated count was due to row duplication caused by the ItemNumber column, which introduced a lower granularity than necessary.
Since your reporting logic considers the combination of Date, Document Number, and SKU as the lowest meaningful level, aggregating at that level is both valid and recommended.Given that these fields are located in separate dimension tables, using the corresponding foreign keys from the 'Outbound Delivery' fact table is the most efficient and model-aligned solution.
Given that these fields reside in separate dimension tables, using the corresponding foreign keys from the 'Outbound Delivery' fact table is the most efficient and model aligned solution.
Corrected Count =
COUNTROWS(
SUMMARIZE(
'Outbound Delivery',
'Outbound Delivery'[DateKey],
'Outbound Delivery'[OutboundDeliveryDocumentKey],
'Outbound Delivery'[ProductKey]
)
)We'll continue exploring if there are further opportunities or edge cases to enhance this further, but for now, your current solution is accurate and reliable.
If the solution worked for you, please click Accept as Solution and feel free to leave a Kudos for visibility.
Thank you,
Sahasra.
Hi Justas4478,
Thank you burakkaragoz for your inputs for the query.
Since you're using a Live Connection to a data cube, you're limited to measures only without calculated columns or data transformations. The higher count issue is likely due to the COUNT function, which counts every non-blank value, including duplicates.
In such instances, rather than counting the raw field directly, it is advisable to count a uniquely identifying field, such as a delivery ID or transaction number, if available in your dataset. This approach ensures that each logical row is counted only once, even if repeated due to data model joins.
If your objective is to count only unique values in a specific column, using a distinct count measure would be more suitable. This method counts only the unique non-blank entries, thereby avoiding duplication from repeated values.
Also, filter context from the visual itself can influence the result. If your table visual includes columns from related tables, Power BI may duplicate rows in the result set due to relationship behavior in the model. It is necessary to test your count measure on a simplified visual with fewer columns to see if the number aligns more closely with your expectations.
Link:DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn
If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Happy to see you here in the Microsoft Fabric Community!
Hi Justas4478,
We wanted to check if you had a chance to review our last reply. Let us know if it helped or if you need more guidance, we're always happy to help further.
If the solution worked for you, please click Accept as Solution and feel free to leave a Kudos for visibility.
Looking forward to hearing from you!
v-sgandrathi I looked a bit more and found why it is counting actual as 2 and not as 1.
This is level of how we look at the data.As you see picked is 2 and count is 2
And this is how it looks when I add additional column called 'ItemNumber'that splits it in to each individual row.
The 'ItemNumber' level is to granular for us and we don't look at it SKU level shown before is as granular as we will be looking.If I understand correctly count needs to ignore 'ItemNumber' and count it as 1 if date, document number and SKU numbers match.
Let me know if my deduction is wrong.Hi Justas4478,
Thanks for the insight, you're absolutely right in your understanding.
After looking, I realized the count is inflated because the data is split by the ItemNumber column, which introduces row-level granularity that we don’t actually need. When I include only Date, Document Number, and SKU Number, the count appears correct at 1, but as soon as I include ItemNumber, the same line shows up twice (or more), thus the count becomes 2.
In our reporting logic, we don’t analyze at the ItemNumber level. Instead, we consider the SKU + Document Number + Date as our lowest granularity.
So to clarify: Yes, the count needs to ignore ItemNumber and count as 1 if the combination of Date, Document Number, and SKU Number matches, regardless of how many ItemNumbers exist under it.
DAX Measure:Corrected Count =
COUNTROWS(
SUMMARIZE(
'Outbound Delivery',
'Outbound Delivery'[Date],
'Outbound Delivery'[Document Number],
'Outbound Delivery'[SKU Number]
)
)Please Accept as solution if this meets your needs and a Kudos would be appreciated.
Thank you,
Sahasra.v-sgandrathi Your solution works, the only thing I had to changes was to use keys(ID) instead of columns since date, doc number, sku number are all in different tables and they all combine in to outbound delvery tablem that has keys.
This is dax after changing from quick check results are correct, but if you think that there is better solution please let me know.COUNTROWS(SUMMARIZE('Outbound Delivery','Outbound Delivery'[DateKey],'Outbound Delivery'[OutboundDeliveryDocumentKey],'Outbound Delivery'[ProductKey]))
