Perform filtering on column value based on condition

[Anonymous]

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]

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]

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

[Samarth_18 · Accepted solution]

@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]

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