Report Table View Different from Data View

[Anonymous]

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

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

[Anonymous]

@v-yalanwu-msft Greetings. Would you be so kind as to provide any advice if possible?

[Transaction Count] = COUNT( trans_id )

[KNP]

Hi @Anonymous - What is the calculation for [Transaction Count]?

[Anonymous]

@KNP sorry...I forgot to add that. The calculation for Transaction count = COUNT( trans_id )

Thank you for your support

[KNP]

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.

[Anonymous]

@KNP the data model is just 2 tables: 1 large fact/dim table (Transactions) + 1 Calendar table

[KNP]

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

[Anonymous]

@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]
    )
)

[Anonymous]

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

[KNP]

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.