Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

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

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Assigned To

ID Name

1Tom
2Richard
3Harry
4Bob

 

Scheduled To

IDName

1Beth
2Jane
2April
3April

 

Yields This Output

 

NameName2CountRowsAssignedCountrowsScheduled

BobApril1 
BobBeth1 
BobJane1 
HarryApril11
HarryBeth1 
HarryJane1 
RichardApril11
RichardBeth1 
RichardJane11
TomApril1 
TomBeth11
TomJane1 

 

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

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

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

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]))

11.png

 

Best Regards

Maggie

 

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
Community Support
Community Support

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.