Identifying sets of account combinations using DAX

[Asil]

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:

• Set 1 including AccountID 1200 and 8000
• Set 2 including AccountID 6000 and 1300
• Set 3 including AccountID 1400 and 800

The calculated table should be like:

Thanks in advance.

Regards,

[v-caliao-msft · Accepted solution]

@Asil,

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

[Asil]

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