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,
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.
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.