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
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.