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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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