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   ...
  • 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.

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!