Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Perform filtering on column value based on condition

Input TableOutput table

 

I have a table where i need to filter a team when it has both requestor and superuser for each CR No. Eg;- CR 130 has both Requestor and superuser so it is present in my output table. CR 128 has only superuser so its not present in my output table.What should i do to get output table.

  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Anonymous , Try this for table:-

    Table 2 = 
SUMMARIZE (
     FILTER (
        'Table',
        CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Superuser" )
            && CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Requestor" )
    ),
    'Table'[CR NO],'Table'[Team]
)

    Output:-

     

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous ,

     

    Create a measure like below and use it as filter on your visual:-

    Measure =
COUNTROWS (
    FILTER (
        'Table',
        CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Superuser" )
            && CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Requestor" )
    )
)

     

    Output:-

     

    Thanks,

    Samarth

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi samarth, Thanks for replying. Is it possible for to generate a new table with above input table with the result.

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous , Try this for table:-

        Table 2 = 
SUMMARIZE (
     FILTER (
        'Table',
        CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Superuser" )
            && CALCULATETABLE ( VALUES ( 'Table'[CR NO] ), 'Table'[Team] = "Requestor" )
    ),
    'Table'[CR NO],'Table'[Team]
)

        Output:-