Match across multiple rows and columns

[Antje]

I'm new to Power Bi and trying to create a formula that will return an equipment number if:

- an equipment number in column Equipment is listed more than once  AND
- at least one line of the matching equipment numbers under column Location has "Showroom" or "Training" AND
- at least one line of the matching equipment numbers under column Invoice Date has a date AND
- at least one line of the matching equipment numbers under column Order Type has the keyword "Sale"

[Antje]

Thank you so much, Samarth! This worked perfectly.

[Samarth_18 · Accepted solution]

Hi @Antje , 

Create a custom column with below code and use it as filter on your visual:-

Column =
VAR _count =
    COUNTROWS (
        FILTER (
            'Table (5)',
            'Table (5)'[Eqipment] = EARLIER ( 'Table (5)'[Eqipment] )
        )
    )
VAR _location =
    COUNTROWS (
        FILTER (
            'Table (5)',
            'Table (5)'[Eqipment] = EARLIER ( 'Table (5)'[Eqipment] )
                && 'Table (5)'[Location] IN { "Showroom", "Location" }
        )
    )
VAR _invoice_Date =
    COUNTROWS (
        FILTER (
            'Table (5)',
            'Table (5)'[Eqipment] = EARLIER ( 'Table (5)'[Eqipment] )
                && 'Table (5)'[Invoice date] <> BLANK ()
        )
    )
VAR _ordertype =
    COUNTROWS (
        FILTER (
            'Table (5)',
            'Table (5)'[Eqipment] = EARLIER ( 'Table (5)'[Eqipment] )
                && 'Table (5)'[Order type] IN { "Sale" }
        )
    )
RETURN
    IF (
        _count >= 2
            && _location >= 1
            && _invoice_Date >= 1
            && _ordertype >= 1,
        1,
        0
    )

output:-

Thanks,

Samarth