Forum Discussion

kevlarmpowered's avatar
kevlarmpowered
Icon for Helper I rankHelper I
8 years ago

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) an...
Need Help
kevlarmpowered's avatar
kevlarmpowered
Icon for Helper I rankHelper I
8 years ago

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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
    kevlarmpowered
    Icon for Helper I rankHelper I
    8 years ago

     

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

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      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's avatar
        kevlarmpowered
        Icon for Helper I rankHelper I
        8 years ago

         

        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.