Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Asil
Frequent Visitor

Identifying sets of account combinations using DAX

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.

Transaction table.PNG


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:

Resulting table.PNG

 

Thanks in advance.

 

Regards,

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@Asil,

 

Create a table by using the DAX below.

Table =
SUMMARIZE (
    'Transaction',
    'Transaction'[TransactionID],
    "ConcatAccountID", CONCATENATEX ( 'Transaction', 'Transaction'[AccountID], "&" )
)

Capture.PNG

 

Create a table by enter data
Capture1.PNG

Create a calculated column.

count =
CALCULATE (
    COUNT ( 'Table'[TransactionID] ),
    FILTER ( 'Table', 'Table'[ConcatAccountID] = Table1[AccountID] )
)

 

Capture2.PNG

 

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@Asil,

 

Create a table by using the DAX below.

Table =
SUMMARIZE (
    'Transaction',
    'Transaction'[TransactionID],
    "ConcatAccountID", CONCATENATEX ( 'Transaction', 'Transaction'[AccountID], "&" )
)

Capture.PNG

 

Create a table by enter data
Capture1.PNG

Create a calculated column.

count =
CALCULATE (
    COUNT ( 'Table'[TransactionID] ),
    FILTER ( 'Table', 'Table'[ConcatAccountID] = Table1[AccountID] )
)

 

Capture2.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.