Forum Discussion

laverma's avatar
laverma
Regular Visitor
3 years ago
Solved

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

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

 

Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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

5 Replies

  • laverma's avatar
    laverma
    Regular Visitor
    3 years ago

    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 ) )
  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    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