Creating a measure for tables with no relationship

[jimmyfromus]

Hi, 

I've 2 tables with no relationship between them, I would like to create a measure to add one column, Membership from table 2 to table 1. Table 1 is on the left and Table 2 is on the right. 

I created this measure in table 1: 

Committee Measure =

VAR __selectedcontact = SELECTEDVALUE ( 'First Table' [contactid] )

VAR __selectedcompany = SELECTEDVALUE ( 'First Table' [companyid] )

VAR __Committee = CALCULATE(MAX('Second Table'[Committee]),'Second Table'[companyid]=__selectedcompany,'Second Table'[contactid]=__selectedcontact)

RETURN __Committee

It doesn't seem to pick up all the values, as for example I would expect the 4th last line for company a in table 1 to have a committee value of dancing.

I've included the pbix file: pbix file 

[PaulDBrown]

@jimmyfromus 

If you are happy to do this in a visual (as opposed to adding a new column to a table) you can use the TREATAS function

[AlB]

@jimmyfromus 

You should do this in Power Query.

It will not work on  a visual without modifying the underlying First table, since you seem to be adding rows. You can create a new calculated table:

First Table B =
GENERATE (
    'First Table',
    VAR contactCompany_ = 'First Table'[contact/company]
    RETURN
        CALCULATETABLE (
            DISTINCT ( 'Second Table'[Committee] ),
            FILTER (
                ALL ( 'Second Table'[company], 'Second Table'[contact] ),
                LEN ( contactCompany_ ) > 0
                    && ( 'Second Table'[contact] = contactCompany_
                    || 'Second Table'[company] = contactCompany_ )
            )
        )
)

 I reiterate that this should be done in PQ.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jimmyfromus]

@AlB Thank you. I'm not sure how to create a calculated table with DAX in power query? Could you please advise? When I create the table via modeling and new table, I get the following but a few rows are missing from membership number 29 to 36. Thanks again. 

[AlB · Accepted solution]

@jimmyfromus 

Calculated table exactly as you have created it, not in PQ.

PQ is another option I recommended but not the solution provided

Try GENERATEALL instead if you want the rows with no committee as well:

First Table B V2 = 
GENERATEALL (
    'First Table',
    VAR contactCompany_ = 'First Table'[contact/company]
    VAR resT_ =
        CALCULATETABLE (
            DISTINCT ( 'Second Table'[Committee] ),
            FILTER (
                ALL ( 'Second Table'[company], 'Second Table'[contact] ),
                LEN ( contactCompany_ ) > 0
                    && ( 'Second Table'[contact] = contactCompany_ || 'Second Table'[company] = contactCompany_ )
            )
        )
 RETURN     
    resT_)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jimmyfromus]

@AlB Thank you, that works well. One last question. Can I add more columns besides committee? If I wanted to add Membership for example?

[jimmyfromus]

@AlB I used Summarize instead of Distinct and it seems to do the job fine. Thanks again. 

[AlB]

Hi @jimmyfromus 

Please explain the logic behind it. How do you get the committee from Table2? Why should it be "dancing" for that last 4th row??

Show the full expected result

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jimmyfromus]

Table 1 has a company/contact column, whereas table 2 has these columns seperated. So in table 2, company a and contact a have dancing as a committee value. I would like to show this in table 1 where company a has a committee value of dancing and the same for contact a. 

The expected result would be like so.