Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Hi,
Your data has not been pasted properly. Please repaste the data properly.
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.
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
I was trying to do it in DAX... not through M.
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
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |