Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a questions and hope you can help me.
I have a transaction table and would like to identify the unique account combinations (I call it sets) and count the number of unique transactions for each set.
So the table above includes 3 different account sets:
The calculated table should be like:
Thanks in advance.
Regards,
Solved! Go to Solution.
Create a table by using the DAX below.
Table = SUMMARIZE ( 'Transaction', 'Transaction'[TransactionID], "ConcatAccountID", CONCATENATEX ( 'Transaction', 'Transaction'[AccountID], "&" ) )
Create a table by enter data
Create a calculated column.
count = CALCULATE ( COUNT ( 'Table'[TransactionID] ), FILTER ( 'Table', 'Table'[ConcatAccountID] = Table1[AccountID] ) )
Regards,
Charlie Liao
Create a table by using the DAX below.
Table = SUMMARIZE ( 'Transaction', 'Transaction'[TransactionID], "ConcatAccountID", CONCATENATEX ( 'Transaction', 'Transaction'[AccountID], "&" ) )
Create a table by enter data
Create a calculated column.
count = CALCULATE ( COUNT ( 'Table'[TransactionID] ), FILTER ( 'Table', 'Table'[ConcatAccountID] = Table1[AccountID] ) )
Regards,
Charlie Liao
Hi Charilie,
Thank you for you reply.
I was hoping to avoid using CONCATENATEX due to the performance issues when I try to analyse +1M transactions but I guess there are no other alternatives at the moment. The solution you provided works for me. I'm going to change it slightly and use SUMMARIZECOLUMN instead.
Thanks again!
Regards,
Asil
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |