Table filter based on distinct value post filter based on one column - Logical

[laverma]

I have a source table below

1. I need to filter the table with the column as 'Assigned'(My assigned have userprinicpalname from office 365 and azure) = UserPrincipalName, with the result below

2. Get distinct from the 'Plan name' as below

3. Lastly with the above distinct value, filter the master table and get data as below

To summarize more

Get the distinct plan name of a user and apply the filter on the master table to get the output of all plans and actions of all users who are part of the same plan as the filtered user is

[laverma]

@PaulDBrown Worked like a charm. Thanks a lot.

I did the following changes to get the currently logged-in user principal name and match it with the email address from the table, rest measures took care of it. 

User 1 Plan Name =

VAR _U1 =

    CALCULATETABLE (

        VALUES ( Sheet1[Planner_Name] ),

        ALL ( Sheet1 ),

        Sheet1[Email_Address] = USERPRINCIPALNAME()

    )

VAR _All =

    CALCULATETABLE ( VALUES ( Sheet1[Planner_Name] ), ALL ( Sheet1[Email_Address] ) )

RETURN

    COUNTROWS ( INTERSECT ( _All, _U1 ) )

[laverma]

@PaulDBrown I will implement same and update back.

[PaulDBrown · Accepted solution]

See if this works.

A) If the user is hard-coded: Create the following measure to use as a filter in the filter pane, setting the value to 1:

User 1 Plan Name =
VAR _U1 =
    CALCULATETABLE (
        VALUES ( fTable[Plan Name] ),
        ALL ( fTable ),
        fTable[Assigned] = "User 1"
    )
VAR _All =
    CALCULATETABLE ( VALUES ( fTable[Plan Name] ), ALL ( fTable[Assigned] ) )
RETURN
    COUNTROWS ( INTERSECT ( _All, _U1 ) )

B) If you need the User selection to be dynamic:

Create a new, independent table for Users (I've called mine 'Select Users"). Leave this new table unrelated in the model:

Select Users = 
DISTINCT(fTable[Assigned])

Use this measure as a filter in the filter pane and set the value to 1:

Sel User Plan Name =
VAR _U1 =
    CALCULATETABLE (
        VALUES ( fTable[Plan Name] ),
        FILTER (
            ALL ( 'fTable' ),
            fTable[Assigned] IN VALUES ( 'Select Users'[Sel Assigned] )
        )
    )
VAR _All =
    VALUES ( fTable[Plan Name] )
RETURN
    COUNTROWS ( INTERSECT ( _All, _U1 ) )

Attached is the sample PBIX file

[laverma]

@PaulDBrown Could you please help  ?

[laverma]

@amitchandak