Forum Discussion
Post ProdigyCount 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.
Post Prodigyv-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.
Community SupportHi 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.
- Justas4478
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]))- v-sgandrathi
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.