Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Count counting some items twice

Hi, I have count measure. COUNT('Outbound Delivery'[Actual]) But some of the values it is counting twice. The actual count should be 230954 I am not sure why it is doing that. Is it faul o...
Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

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!

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

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!