Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Grouping and Full outer join

EVALUATE ADDCOLUMNS( VALUES('Inc_$Item Ledger Entry'[Item No_]), "PurchReq",CALCULATE(SUM(' Inc_$Requisition Line'[Quantity (Base)])) )   I am trying to get the sum  of [Quantity (Base)(integ...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

Thank you for your reply.

 

Yes tables are connected using Item NO column as that is the primary key.

 

I tried using SUMMARIZECOLUMNS,

 

Getting below error.

 

"Multiple columns can not be converted to scalar value"

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous I only seem to get that error when I try to place the formula in a measure by itself. Are you using it in a measure or creating a new table? The formula should work when you use it to create a new table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I am creating new table.Actual sql query is as below.I m trying to group sum by Item no and status.In sql ,tables are joined using Item no which is primary key.But in power BI 'One to One ' and 'Many to one' realtionship is created with DATE table using respective date column from each.

     

    Select pol.[Item No_],pol.Status,
    sum(rl.[Quantity (Base)]) as PurchReq,
    sum(pol.[Remaining Qty_ (Base)]) as OrderReceipt
    from [Inc_$Prod_ Order Line] pol
    join [Inc_$Requisition Line] rl on rl.No_=pol.[Item No_]
    group by pol.[Item No_],pol.Status

     

     

    Alos I tried using NATURALINNERJOIN but not getting any values.Please suggest.