Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Report Table View Different from Data View

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 I...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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

 

Thank you for your support

KNP's avatar
KNP
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      KNP
      Super User
      5 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • KNP's avatar
      KNP
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

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