Count counting some items twice

[Justas4478]

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 of the measure.
I did try different count but all return same result.

[v-sgandrathi]

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!

[Justas4478]

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

[v-sgandrathi]

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.

[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 · Accepted solution]

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]

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!

[Rupak_bi]

please share sample data representing the model. Without looking to the model, it cant be solved. 

[burakkaragoz]

Hi @Justas4478 ,

This is a classic scenario in Power BI and other data tools, where the COUNT measure counts some items twice—almost always due to the underlying data model and table relationships, not the measure itself. Let’s break down the possible reasons and how to fix it:

Why the COUNT is Incorrect

1. Duplicates in Source Table:
   If your [Actual] column or the table 'Outbound Delivery' contains duplicate rows for the same item, COUNT will count each row, even if the value is the same.

2. Many-to-Many or Incorrect Relationships:
   If your data model has many-to-many relationships, or if the 'Outbound Delivery' table is related to other tables in a way that creates duplicate rows in the result (for example, due to improper joins or ambiguous relationships), the COUNT will sum up those duplicates.

3. Expanded Data in Visual:
   If your visual’s granularity is higher than the actual base data, (e.g., you are grouping or joining with another table), the COUNT will reflect that expanded set.

How To Diagnose

• Check the Data Table:
  Go to Data view and use "Remove Duplicates" on the [Actual] column. See if duplicate rows exist.
• Look at Relationships:
  In Model view, check all relationships connected to 'Outbound Delivery'. If there are more than one, or if there are bidirectional or many-to-many relationships, these can multiply your row count.
• Drill Down:
  Filter a problematic value (e.g., 2.00) and check all rows—do you see multiple rows with the same value? If yes, that’s your source of duplication.

How To Fix

A. If you want to count unique values (distinct):

Use the DISTINCTCOUNT function in DAX:

DAX

Count of Unique Outbound Deliveries = DISTINCTCOUNT('Outbound Delivery'[Actual])

This will count each unique [Actual] value only once.

B. If you want to count unique rows:

If you have a row ID or unique key, use:

DAX

Count of Unique Rows = DISTINCTCOUNT('Outbound Delivery'[RowID])

C. To find the duplicates:

You can add a calculated column:

DAX

Duplicate Check = 
CALCULATE(
    COUNTROWS('Outbound Delivery'),
    ALLEXCEPT('Outbound Delivery', 'Outbound Delivery'[Actual])
)

Then filter where [Duplicate Check] > 1 to find which values are duplicated.

General Advice

• The COUNT measure is not “wrong”—it simply counts all rows, including duplicates created by your data structure or relationships.
• Use DISTINCTCOUNT to get unique items.
• Always check relationships and data granularity in your model.

---

If you share a sample of your data model or clarify the relationships, I can provide even more tailored advice. But with these steps you should be able to pinpoint and fix the double-counting issue.

Let me know if you need detailed step-by-step instructions!

[Justas4478]

@burakkaragoz Really good explanation, but I can't do calculated columns only measures.
Since 3rd party is managing data cube and it is set to live connection mode.
Forcing to use only measures.