topic Data drill down a table in DAX Commands and Tips

Data drill down a table

sushdutta — Mon, 23 Sep 2024 04:51:20 GMT

Hi All,

Kindly could you provide some guidance how i can solve this scenario

I have a table of say four columns.

Acc Customer Security Expiry

1 123 ABC 24/12/2024

2 321 CBA 25/12/2021

3 142 ADB 21/03/2020

4 321 BCA 21/04/2026

4 333 BCA 21/04/2026

5 333 ACD 21/04/2026 etc.

I am looking for a query to identify all accounts by customer 321 and if joint ownership then to also fetch all account of joint owners. Is there a way i can do it in DAX?

Thanks and Regards

Re: Data drill down a table

ahadkarimi — Mon, 23 Sep 2024 14:57:30 GMT

Hi sushdutta, give this a try, and if you encounter any issues, let me know. change YourTableName to your table name.

AllRelevantAccounts = VAR TargetCustomer = 321 VAR TargetCustomerAccounts = CALCULATETABLE( VALUES('YourTableName'[Acc]), 'YourTableName'[Customer] = TargetCustomer ) VAR JointOwners = CALCULATETABLE( DISTINCT('YourTableName'[Customer]), 'YourTableName'[Acc] IN TargetCustomerAccounts, 'YourTableName'[Customer] <> TargetCustomer ) VAR AllCustomers = UNION( {TargetCustomer}, JointOwners ) RETURN FILTER( 'YourTableName', 'YourTableName'[Customer] IN AllCustomers )

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

Re: Data drill down a table

Anonymous — Tue, 24 Sep 2024 02:40:03 GMT

Hi sushdutta

Please try this:

Table:

First of all, create a calculated table:

Table 2 = SUMMARIZE('Table','Table'[Acc],'Table'[Customer])

Create a Slicer with 'Table2'[Customer]:

Then add a measure:

MEASURE = VAR _vtable = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Table 2', ALLSELECTED ( 'Table' ) ), 'Table 2'[Acc] = 'Table'[Acc] ), 'Table'[Customer] ) RETURN IF ( MAX ( 'Table'[Customer] ) IN _vtable, 1 )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.