Lookup values matching more than one columns

[SintRa]

Hi,

I have the following two tables and I would like to create:

1) A measure or column (not sure what is best) to return the number of companies
2) A calculate table to get records in table 1 which do no exist in table2 matching and returning user and Role columns

can you help me? I tried with Vlookup but I am not sure how to return and search using two columns

[Anonymous · Accepted solution]

Hi, @SintRa 

Maybe you can try the following DAX

First, create a new column:

Column =
VAR _virtualTable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS ( 'Table1', "User1", 'Table1'[User], "Role1", 'Table1'[Role] ),
            SELECTCOLUMNS (
                'Table2',
                "User2", 'Table2'[User],
                "Role2", 'Table2'[Role],
                "Company", 'Table2'[Company]
            )
        ),
        [Role1] = [Role2]
            && [User1] = [User2]
    )
RETURN
    IF (
        COUNTX (
            FILTER ( _virtualTable, 'Table1'[User] = [User1] && 'Table1'[Role] = [Role1] ),
            [Company]
        )
            = BLANK (),
        0,
        COUNTX (
            FILTER ( _virtualTable, 'Table1'[User] = [User1] && 'Table1'[Role] = [Role1] ),
            [Company]
        )
    )

Regarding the second question, create a calculated table:

Table1NotInTable2 =
EXCEPT (
    SELECTCOLUMNS ( 'Table1', "User", 'Table1'[User], "Role", 'Table1'[Role] ),
    SELECTCOLUMNS ( 'Table2', "User", 'Table2'[User], "Role", 'Table2'[Role] )
)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SintRa]

Thanks @Anonymous, it helps a lot! I could not reach this solution!!!

[johnbasha33]

@SintRa 
Number of Companies = COUNTROWS(DISTINCT('Table 1'[Company]))

Missing Records =
EXCEPT(
'Table 1',
'Table 2'
)