Forum Discussion

Antje's avatar
Antje
Frequent Visitor
4 years ago
Solved

Match across multiple rows and columns

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"



  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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

     

  • Antje's avatar
    Antje
    Frequent Visitor
    4 years ago

    Thank you so much, Samarth! This worked perfectly.