Data drill down a table

[sushdutta]

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

SD

[v-zhengdxu-msft · Accepted solution]

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.

[ahadkarimi]

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!