Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have a table which can have duplicate transaction ID's because of manual comments that can be made by the user. My problem is that the measure that I created is correctly counting the transaction ID's that I am looking for in the Data View but if I bring that same data into a Report Table Visual, the number of rows get summarized (duplicate transaction ID's get summarized into 1) resulting in total number of rows being smaller than the measure. I tried making sure to "Not Summarize" each column but this is still happening when adding dimensions into a report table.
Is there a way for me count the number of transactions that are summarized as 1 transaction? Below is the measure + sample data (current data view output / expected report table output). Goal is to have measure count number of rows like "Expected Report Table".
Any advice will be greatly appreciated.
Measure (The goal of the measure is to get transactions from today -120 days not including today)
CALCULATE(
[Transaction Count],
FILTER(
Transactions,
Transactions[Date] > TODAY() -120
)
)
[Transaction Count] = COUNT(Transactions[trans_id])
Current Data View Output (Notice trans_id 1 & 5) > Current measure counts this number of rows
trans_id | item | comments |
1 | Pizza | Customer paid in cash |
1 | Pizza | Customer paid in cash |
2 | Burger | |
3 | Tacos | Steak and Pork Tacos |
3 | Tacos | Customer has special request |
4 | Wings | |
5 | Pizza | Customer wants Pineapple topping |
5 | Pizza | Customer wants Pineapple topping |
Expected Report Table Visual Output (Notice trans_id 1 & 5) > Goal is to have measure count number of rows for this Report Table Visual
trans_id | item | comments |
1 | Pizza | Customer paid in cash |
2 | Burger | |
3 | Tacos | Steak and Pork Tacos |
3 | Tacos | Customer has special request |
4 | Wings | |
5 | Pizza | Customer wants Pineapple topping |
Solved! Go to Solution.
I found my own solution by updating the inner measure [Transaction Count]. This will summarize transactions that have the same comment but individually count transactions that have distinct comments.
[Transaction Count] =
COUNTROWS(
SUMMARIZE(
Transactions,
Transactions[trans_id],
Transactions[comments]
)
)
I found my own solution by updating the inner measure [Transaction Count]. This will summarize transactions that have the same comment but individually count transactions that have distinct comments.
[Transaction Count] =
COUNTROWS(
SUMMARIZE(
Transactions,
Transactions[trans_id],
Transactions[comments]
)
)
@v-yalanwu-msft Greetings. Would you be so kind as to provide any advice if possible?
[Transaction Count] = COUNT( trans_id )
Hi @win_anthony - What is the calculation for [Transaction Count]?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@KNP sorry...I forgot to add that. The calculation for Transaction count = COUNT( trans_id )
Thank you for your support
Possibly, DISTINCTCOUNT( trans_id ) will work, depending on what else you're using that measure for.
Edit: may need to see data model with relationships if it isn't that simple.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@KNP the data model is just 2 tables: 1 large fact/dim table (Transactions) + 1 Calendar table
@win_anthony - still need more info to be able to offer any guidance. From your original post, I can't figure out what is causing transactions 1 and 5 to "appear" to be duplicated and therefore how to write the measure or re-model in Power Query.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@KNP thank you so much for your support. I was able to find my own solution by updating my inner measure [Transaction Count]. Ultimately, I figured out that by using COUNTROWS + SUMMARIZE, it will summarize any transaction that has the same comment but individually count any transaction that has distinct comments.
Regardless, thank you so much for your support!
COUNTROWS(
SUMMARIZE(
Transactions,
Transactions[trans_id],
Transactions[comments]
)
)
@KNP I tried DISTINCTCOUNT( trans_id ) but that did not work well. If you look at "Expected Report Table Output", using DISTINCTCOUNT( trans_id ) will only give 1 count for trans_id = 3.
I think I need to see a more complete set of data and the model with relationships.
My gut feeling, I'd be doing more data modelling in Power Query and possibly separating out the comments or concatenating them into one column, but I'm guessing at this point.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
130 | |
80 | |
55 | |
39 | |
35 |
User | Count |
---|---|
205 | |
81 | |
72 | |
55 | |
50 |