Forum Discussion

sushdutta's avatar
sushdutta
New Member
1 year ago
Solved

Data drill down a table

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

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    1 year ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.