How to search a group of rows in other table

[Danielecc]

Hi everyone, 

I would like to know how we can search a group of rows from table1 in table 2.

The Group to search is table2, group by "AREA", and that group must be searched in table1, , the example is:

O.Mena is not in "result expected" because he got only 1 row of the group and 1 row in the other group, to be in result expected you have to match all rows of the group.

The tables are:

Table1:

USER	Country	Organization	Item	Value
D.Celis	Chile1	Company_G7	Quantity	4
D.Celis	Chile1	Company_G9	Product	PRD13
J.Smith	Chile2	Company_G7	Product	PRD02
R. Perez	Chile2	Company_G9	Product	PRD15
R. Perez	Chile2	Company_G5	Unit	Meter
R. Perez	Chile3	Company_G8	Quantity	2
R. Perez	Chile3	Company_G5	Product	PRD01
R. Perez	Chile4	Company_G5	Unit	Meter
C.Lezma	Peru1	Company_H2	Quantity	4
C.Lezma	Peru1	Company_I3	Long	LA2
C.Lezma	Peru2	Company_J4	Product	ART23
C.Lezma	Peru2	Company_H2	Quantity	2
O. Mena	Peru3	Company_H2	Quantity	2
O. Mena	Peru3	Company_H2	Product	ART12
O. Mena	Peru4	Company_L3	Quantity	3
O. Mena	Peru4	Company_L4	Quantity	2

Table2:

City	Area	Organization	Item	Value
Santiago	L.Condes	Company_G7	Quantity	2
Santiago	Ñuñoa	Company_G7	Product	PRD02
Santiago	Vitacura	Company_G8	Quantity	2
Santiago	Vitacura	Company_G9	Product	PRD15
Lima	Chorrillos	Company_H2	Quantity	2
Lima	Chorrillos	Company_I3	Long	LA2
Lima	Chorrillos	Company_J4	Product	ART23
Lima	San Isidro	Company_H2	Quantity	2
Lima	San Isidro	Company_H2	Product	ART10
Lima	Lince	Company_L3	Quantity	2
Lima	Lince	Company_L4	Quantity	2

Thanks a lot and best regards...

[smpa01]

@Danielecc  you can create a measure like this

City =
MAXX (
    ADDCOLUMNS (
        Table1,
        "X",
            MAXX (
                FILTER (
                    Table2,
                    Table2[Organization] = EARLIER ( Table1[Organization] )
                        && Table2[Item] = EARLIER ( Table1[Item] )
                        && Table2[Value] = EARLIER ( Table1[Value] )
                ),
                Table2[City]
            )
    ),
    [X]
)

which will give you the following

But what did you mean by - O.Mena is not in "result expected" because he got only 1 row of the group and 1 row in the other group, to be in result expected you have to match all rows of the group

[Danielecc]

@smpa01 , this imagen show why O.Mena is not on result expected table:

Thanks again for your help...

[Danielecc]

Wooooooow @smpa01 Something like that is what I need.

About your question:

O. Mena not must be in the table of result expected, because the Group "San Isidro" from "Lima" has 2 rows and O.Mena get match only with one of that 2 rows.

The same case with "Lince" from "Lima".

Only J.Smith, R.Perez and C.Lezma match all rows group by "Area" on table2. (like show the arrows).

Thanks again @smpa01 that was very close.

[smpa01 · Accepted solution]

@Danielecc  can be achieved with a measure like this

_Value = 
VAR _city =
    MINX (
        ADDCOLUMNS (
            Table1,
            "X",
                MINX (
                    FILTER (
                        Table2,
                        Table2[Organization] = EARLIER ( Table1[Organization] )
                            && Table2[Item] = EARLIER ( Table1[Item] )
                            && Table2[Value] = EARLIER ( Table1[Value] )
                    ),
                    Table2[City]
                )
        ),
        [X]
    )
VAR _area =
    MINX (
        ADDCOLUMNS (
            Table1,
            "X",
                MINX (
                    FILTER (
                        Table2,
                        Table2[Organization] = EARLIER ( Table1[Organization] )
                            && Table2[Item] = EARLIER ( Table1[Item] )
                            && Table2[Value] = EARLIER ( Table1[Value] )
                    ),
                    Table2[Area]
                )
        ),
        [X]
    )
VAR _count1 =
    COUNTX (
        FILTER ( Table2, Table2[City] = _city && Table2[Area] = _area ),
        Table2[City]
    )
VAR _count2 =
    CALCULATE (
        COUNTX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    Table1,
                    "@city",
                        MINX (
                            FILTER (
                                Table2,
                                Table2[Organization] = EARLIER ( Table1[Organization] )
                                    && Table2[Item] = EARLIER ( Table1[Item] )
                                    && Table2[Value] = EARLIER ( Table1[Value] )
                            ),
                            Table2[City]
                        )
                ),
                "@Area",
                    MINX (
                        FILTER (
                            Table2,
                            Table2[Organization] = EARLIER ( Table1[Organization] )
                                && Table2[Item] = EARLIER ( Table1[Item] )
                                && Table2[Value] = EARLIER ( Table1[Value] )
                        ),
                        Table2[Area]
                    )
            ),
            [@city]
        ),
        ALLEXCEPT ( Table1, Table1[USER] )
    )
RETURN
    IF (
        _count1 - _count2 = 0,
        MINX (
            ADDCOLUMNS (
                Table1,
                "X",
                    MINX (
                        FILTER (
                            Table2,
                            Table2[Organization] = EARLIER ( Table1[Organization] )
                                && Table2[Item] = EARLIER ( Table1[Item] )
                                && Table2[Value] = EARLIER ( Table1[Value] )
                        ),
                        Table2[Value]
                    )
            ),
            [X]
        )
    )

pbix is attached

[Danielecc]

@smpa01  OMG man....

It is not enough to accept as a solution, I also had to respond and thank you for this giant code, the time you used to respond to the post. And I swear I didn't see ANYTHING similar on any website as a solution, I almost give up.

Thank you a lot!!!

[Greg_Deckler]

@Danielecc Sure, you can use LOOKUPVALUE or MAXX(FILTER(...),...)

[Danielecc]

Thanks @Greg_Deckler  I am searching about LOOKUPVALUE and MAXX(FILTER(...),...) to see if this solve the example.

Best regards...