topic Re: Lookup values matching more than one columns in DAX Commands and Tips

Lookup values matching more than one columns

SintRa — Fri, 05 Apr 2024 05:24:07 GMT

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

Re: Lookup values matching more than one columns

johnbasha33 — Fri, 05 Apr 2024 09:26:32 GMT

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

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

Re: Lookup values matching more than one columns

Anonymous — Mon, 08 Apr 2024 06:25:31 GMT

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.

Re: Lookup values matching more than one columns

SintRa — Mon, 08 Apr 2024 11:45:13 GMT

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