Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Perform filtering on column value based on condition

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

     

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:-

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you Samarth for both the logics. Both are correct and working fine.