Power BI Aggregations – How to Aggregate COUNT of Unique Transactions?

[Mithril1991]

Power BI Aggregations – How to Aggregate COUNT of Unique Transactions?

Introduction

Hi everyone,

I’m working on a Power BI composite model with managed aggregations, and I’m trying to count unique transactions (TRANSACTION_KEY) across different aggregation levels (transactional → daily → monthly).

I have precomputed TRANSACTION_COUNT at the aggregated levels, done by the backend team with complex groupings. However, Power BI does not recognize it correctly in aggregations, even though standard SUM-based measures work fine on the aggregations.

---

Model Setup

I have four tables:

• FT_SALES_TRANSACTIONAL_DQ (DirectQuery, order item-level data) – Each row is an order item, with TRANSACTION_KEY identifying unique transactions.
• FT_SALES_TRANSACTIONAL_LATEST (Import, transactional-level table) – Stores the latest two months of transactions. Let's ignore this for now.
• FT_SALES_DAILY (Import, pre-aggregated table) – Aggregated at the daily level, with TRANSACTION_COUNT precomputed by the backend.
• FT_SALES_MONTHLY (Import, pre-aggregated table) – Aggregated at the monthly level, with TRANSACTION_COUNT precomputed by the backend.

The key point here is that TRANSACTION_COUNT in FT_SALES_DAILY and FT_SALES_MONTHLY is not just a simple DISTINCTCOUNT. It is calculated with complex grouping logic to ensure correctness across multiple dimensions.

What I Want to Achieve

1. Accurate transaction counts across all levels:

   • Transactional: Count distinct TRANSACTION_KEY in FT_SALES_TRANSACTIONAL_DQ.
   • Daily: Use precomputed TRANSACTION_COUNT from FT_SALES_DAILY.
   • Monthly: Use precomputed TRANSACTION_COUNT from FT_SALES_MONTHLY.

2. Aggregation-Aware Model:

   • Power BI should automatically switch between tables based on granularity.
   • This works fine for standard SUM-based measures.

3. Avoid Performance Issues:

   • Avoid DISTINCTCOUNT in DirectQuery, as it is too slow.
4. Ensure Power BI uses precomputed aggregations instead.

What I Tried

1. Created a Numeric Field (1 AS TRANSACTION_COUNT) in the Transactional Table

I tried converting TRANSACTION_KEY into a numeric countable field:

SELECT TRANSACTION_KEY, 1 AS TRANSACTION_COUNT FROM FT_SALES_TRANSACTIONAL_DQ;

My idea was that if I SUM this field in the aggregated tables, Power BI should recognize it as a valid aggregation. This does not work.

---

2. Used Precomputed TRANSACTION_COUNT in Aggregated Tables (Done by Backend Team)

• TRANSACTION_COUNT is not a simple DISTINCTCOUNT. It is precomputed at the aggregated level with complex business logic.
• Each table (FT_SALES_DAILY, FT_SALES_MONTHLY) already contains the correct transaction count for its level.

I did NOT manually compute DISTINCTCOUNT in SQL—this is handled at the backend aggregation layer.

---

3. Set Up Aggregation Rules in Power BI

• FT_SALES_MONTHLY → TRANSACTION_COUNT mapped as SUM(), referencing FT_SALES_TRANSACTIONAL_DQ[TRANSACTION_COUNT].
• FT_SALES_DAILY → TRANSACTION_COUNT mapped as SUM(), referencing FT_SALES_TRANSACTIONAL_DQ[TRANSACTION_COUNT].
• Aggregation precedence:
  • Monthly: Precedence = 20 (Power BI tries to use this first).
  • Daily: Precedence = 10.

My expectation was that Power BI should use SUM(TRANSACTION_COUNT) from FT_SALES_MONTHLY first, then FT_SALES_DAILY, and only fall back to DirectQuery when necessary. However, this does not work as expected.

---

Where It Fails

Power BI does NOT match TRANSACTION_COUNT to the aggregated tables and always falls back to DirectQuery.

• SUM-based measures like NET_SALES_EXCL_VAT work perfectly.
• TRANSACTION_COUNT, mapped as SUM, is ignored.
• SQL Profiler shows that Power BI fails to match TRANSACTION_COUNT, even though precedence rules are correct.

---

The Big Question

• How should I structure TRANSACTION_COUNT so Power BI correctly recognizes it in aggregations?
• Has anyone successfully aggregated transaction counts across different levels in Power BI?
• Is there a workaround to make Power BI recognize SUM(TRANSACTION_COUNT) and switch to precomputed values instead of falling back to DirectQuery?

Would really appreciate insights from anyone who has solved this.

Summary of the Issue

• Power BI Aggregations work perfectly for SUM-based fields.
• Power BI fails to recognize TRANSACTION_COUNT as a valid aggregation field.
• SUM mapping on TRANSACTION_COUNT does not work, even though the field is precomputed.
• Looking for the best way to structure transaction counts in an aggregation-aware model.

If anyone has found a way to do this, I’d love to hear it!

[v-veshwara-msft]

Hi @Mithril1991 ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @Mithril1991 ,

Thanks for reaching out to Microsoft Fabric Community,

Just wanted to check if your query has been resolved. If not, please consider the following  that might help address the issue you're facing with the TRANSACTION_COUNT aggregation in your composite model.

From your description, it looks like you're trying to map a SUM(TRANSACTION_COUNT) from the pre-aggregated Import tables to a DISTINCTCOUNT(TRANSACTION_KEY) at the DirectQuery level, where TRANSACTION_KEY is a string. Unfortunately, Power BI's managed aggregation engine currently does not support mapping SUM of a numeric field to a DISTINCTCOUNT over a string column. This mismatch in aggregation semantics and data types is likely the reason Power BI is falling back to DirectQuery.

Suggestions to Work Around This Limitation
1. Use a Surrogate Numeric Transaction Key
If possible, consider introducing a numeric surrogate key for transactions in the backend. This allows Power BI to perform a DISTINCTCOUNT on a numeric column, which can then be mapped to SUM(TRANSACTION_COUNT) in the Import tables.

2. Alternatively, you can create a custom DAX measure that handles switching logic manually:

Total Transactions =
SWITCH (
TRUE(),
HASONEVALUE('FT_SALES_MONTHLY'[Month]), SUM('FT_SALES_MONTHLY'[TRANSACTION_COUNT]),
HASONEVALUE('FT_SALES_DAILY'[Date]), SUM('FT_SALES_DAILY'[TRANSACTION_COUNT]),
DISTINCTCOUNT('FT_SALES_TRANSACTIONAL_DQ'[TRANSACTION_KEY])
)

This ensures optimized performance when using aggregated visuals, while still falling back to the granular view when needed.

3. Ensure that:

>The column names are consistent across all tables.

>The data types of TRANSACTION_COUNT are numeric across the board.

>Aggregation mappings are correctly defined in the model view.

Precedence is properly set (monthly > daily > transactional).

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-veshwara-msft]

Hi @Mithril1991 ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[Mithril1991]

Hello,

Thank you for reaching out. We solved that issue with specific view and remodelling into simpler import only model after backend team received capacity approval. Thus I have no way of confirming your solution work now, but I accepted it as it explained me the issue quite well and I understand it could most probably help with that solution. 

Thank you

[Anonymous]

Hi @Mithril1991 ,

Did the above suggestions help with your situation? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[bhanu_gautam]

@Mithril1991 Make sure that the TRANSACTION_COUNT field in all tables (FT_SALES_TRANSACTIONAL_DQ, FT_SALES_DAILY, and FT_SALES_MONTHLY) has the same data type and field name. This consistency is crucial for Power BI to recognize and map the fields correctly.

Verify that the aggregation settings in Power BI are correctly configured. Specifically, ensure that the TRANSACTION_COUNT field in FT_SALES_DAILY and FT_SALES_MONTHLY is set to SUM and that these tables are set up as aggregation tables for FT_SALES_TRANSACTIONAL_DQ

n Power BI, go to the Model view and select the FT_SALES_DAILY and FT_SALES_MONTHLY tables. For each table, configure the aggregation settings:
Set the Detail Table to FT_SALES_TRANSACTIONAL_DQ.
Map the TRANSACTION_COUNT field to SUM(TRANSACTION_COUNT) from FT_SALES_TRANSACTIONAL_DQ

Ensure that the aggregation precedence is correctly set. Higher precedence values should be assigned to more aggregated tables. For example, FT_SALES_MONTHLY should have a higher precedence than FT_SALES_DAILY

Define a measure in Power BI to use the precomputed TRANSACTION_COUNT values. For example:

DAX
Total Transactions =
IF (
HASONEVALUE(FT_SALES_MONTHLY[Month]),
SUM(FT_SALES_MONTHLY[TRANSACTION_COUNT]),
IF (
HASONEVALUE(FT_SALES_DAILY[Date]),
SUM(FT_SALES_DAILY[TRANSACTION_COUNT]),
DISTINCTCOUNT(FT_SALES_TRANSACTIONAL_DQ[TRANSACTION_KEY])
)
)

Test the setup by creating visuals that use the Total Transactions measure. Check if Power BI correctly switches between the aggregated tables based on the granularity of the data being visualized.

[Mithril1991]

I believe there is a misunderstanding as to what the exact issue is.

The goal is to sum the TRANSACTION_COUNT field in the aggregated tables. The issue is that in DirectQuery, this field is stored as a string in the format "Country_Number" rather than a numeric value.

The challenge is telling Power BI to use TRANSACTION_COUNT when looking at the aggregated tables but switch to a DISTINCTCOUNT of TRANSACTION_KEY when querying DirectQuery. I believe there should be a way to translate distinct counts onto lower aggregations.

To address your points:

- Aggregation settings are correctly configured for other fields like Revenues and COGS, which work fine since the DirectQuery data type matches the aggregated tables.

- Precedence is set correctly, with monthly at 20, daily at 10, partial transactions at 2, and DirectQuery at 0.

- Defining a measure to manually switch between aggregation tables contradicts the purpose of using a composite model, which is to optimize performance while keeping transactional-level data accessible on demand.

There is another issue where the model is not selecting the correct aggregation table, but that is separate from this post. I might create a new post for that specific issue.

Basically: There are multiple  transaction keys (as string) at lowest granularity, I need to count the distinct number of them to know how many transactions were doen at any given "dimension". In aggregated table, this is precalculated as TRANSACTION_COUNT. 

Sorry for the late response.

Below is snip of the model I am working with