Group Column of locations based on condition from another column

[romoguy15]

Hi,

I have been working to try and come up with a calculated column that can tell me if my list of locations meets a specific condition with a Yes/No statement.

I have a list of locatins with a column of different tickets. The locations are listed more than once but can have two different types of tickets. I am trying to group the locations to say if Location has a PSU and a Non NWR Ticket, "Yes", if not then "No". The calculation column it tried using below as well as several other attempts but it seems i'm not fully understanding how to achieve it.

 = CALCULATE(IF(FILTER(ALL('Sample'[Location], 'Sample'[Ticket]), 'Sample'[Ticket] = "PSU" && "Non NWR"), "Yes", "No"))

Below is the sample data

https://1drv.ms/u/s!AqID1H0nHPOzhAoeXiSYdvCbmb-e?e=4iU40P

Attached is an example snip from excel of what I am getting at

I appreciate the support and any input I can get.

Thank you

[mahoneypat · Accepted solution]

Here is a column expression that shows one way to do it.

Has Both =
VAR vResult =
    CALCULATE (
        DISTINCTCOUNT ( 'Sample'[Ticket] ),
        ALLEXCEPT ( 'Sample', 'Sample'[Location] ),
        'Sample'[Ticket] IN { "PSU", "Non NWR" }
    )
RETURN
    IF ( vResult = 2, "Y", "N" )

Pat

[romoguy15]

@mahoneypat & @Anonymous  thank you both for the solution. Not that I would have figured out the entire equation but I did not stop to think of using VAR. Thank you.

[mahoneypat · Accepted solution]

Here is a column expression that shows one way to do it.

Has Both =
VAR vResult =
    CALCULATE (
        DISTINCTCOUNT ( 'Sample'[Ticket] ),
        ALLEXCEPT ( 'Sample', 'Sample'[Location] ),
        'Sample'[Ticket] IN { "PSU", "Non NWR" }
    )
RETURN
    IF ( vResult = 2, "Y", "N" )

Pat

[Anonymous · Accepted solution]

Here's a faster version that does not rely on CALCULATE:

// T is the name of your table.

[Your Column] = // calculated column
var CurrentLocation = T[Location]
var TicketTable = {"psu", "non nwr"}
var Yes =
    2 = COUNTROWS(
        filter(
            ALL( T[Location], T[Ticket] ),
            T[Location] = CurrentLocation
            &&
            T[Ticket] in TicketTable
        )
    )
return
    if( Yes, "Yes", "No" )