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...
  • PaulDBrown's avatar
    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