One to Many ... not so easy when using columns in a table from both tables

[kevlarmpowered]

Assignments (1)

ID

Assigned To

The values being

Tom

Richard

Harry

Bob 

Schedules (Many)

ID

Scheduled To

Jane

Beth

April

I've done this before (at least I think I have) and I can't remember how to accomplish this feat with the following result.

Assigned To, Scheduled To, Assigned, Scheduled

Name, Name, countrows(Assignments), countrows(Schedules)

When I do it, it repeats all of the ScheduledTo for each AssignedTo rows, so I get something like this (even if there are no rows in the second table).  

Tom Jane 

Tom Beth 

Tom April 

Richard Jane 

Richard Beth 

Richard April 

Harry Jane 

Harry Beth 

Harry April 

Bob Jane 

Bob Beth 

Bob April 

When I reality I want

Tom Beth

Richard Jane

Richard April

Harry April

Bob  blank (because Bob doesn't have related record in the ScheduledTo table)

I'm basically trying to count the rows of each table by the pair of people, but it's not working as I expect

[Ashish_Mathur]

Hi,

Your data has not been pasted properly.  Please repaste the data properly.

[kevlarmpowered]

Assigned To

ID Name

1	Tom
2	Richard
3	Harry
4	Bob

Scheduled To

IDName

1	Beth
2	Jane
2	April
3	April

Yields This Output

NameName2CountRowsAssignedCountrowsScheduled

Bob	April	1
Bob	Beth	1
Bob	Jane	1
Harry	April	1	1
Harry	Beth	1
Harry	Jane	1
Richard	April	1	1
Richard	Beth	1
Richard	Jane	1	1
Tom	April	1
Tom	Beth	1	1
Tom	Jane	1

The blanks in the countrowsscheduled column ideally should not be there because there is no matching pair.

[Ashish_Mathur]

Hi,

U used this M code

let
    Source = Table.NestedJoin(Table1,{"ID Name"},Table2,{"ID Name"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Scheduled To"}, {"Scheduled To"})
in
    #"Expanded Table2"

Here's the result i got

[kevlarmpowered]

I was trying to do it in DAX... not through M.

[v-juanli-msft]

Hi @kevlarmpowered

Assume you have got the third table, thus I can make changes on the table.

Create calculated columns

Column = CONCATENATE(CONCATENATE([Name]," "),[Name2])

Column 2 = CALCULATE(COUNT(Table3[CountrowsScheduled]),ALLEXCEPT(Table3,Table3[Name]))

Column 3 = IF([CountrowsScheduled]=1,[Column],IF([Column 2]=BLANK(),[Name]))

Best Regards

Maggie

[kevlarmpowered]

I ended up doing this to get by for the time being... I was hoping to get by without having to have this extra table floating around in my model, but this gets me by until I find a better way.

Assignments = SELECTCOLUMNS(NATURALLEFTOUTERJOIN ( Assignments, Scheduled ) 

The columns I needed for the calculations

)

It works... but I'm trying to find a way to make the table not exist.

[v-juanli-msft]

Hi @kevlarmpowered

How do you create relationships between two tables? are they linked by ID? how does the ID match each other?

From your information, I can't reproduce your scenario.

Could you share me some data?

Best Regards

Maggie