Multiple records with same id but must filter out just the 1 matching row based on criteria.

[GatorTB]

I need to create a new report using this sample list below. 

Each CID has 4 possible SignOffs. CID 5000 shows all SignOff options.

For the same CIDs, show me the ones that have a record for SBSLA while also NOT having a record for SBSUPPORTBETA or SBFUNCTIONALITYBETA.  So, I don't want to see CID 5000, nor CID 3144.  I do want to see 4461.

Your help is appreciated!

[jdbuchanan71 · Accepted solution]

@GatorTB 

I think this will give you what you are looking for.

Filter Measure = 
VAR _SBSLA = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Signoff] = "SBSLA" )
VAR _OTHER = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Signoff] IN {"SBFUNCTIONALITYBETA","SBSUPPORTBETA"} )
RETURN 
IF ( AND ( _SBSLA <> 0, _OTHER = 0 ), "Show" )

[GatorTB]

Thank you!  That worked perfectly!