Forum Discussion

Florian_Kluge's avatar
Florian_Kluge
Regular Visitor
4 years ago
Solved

Combining Addcolumns, Related, Summarize and Calculatetable

Dear all,

 

I'm quite new to Power BI and try to deal with DAX more and more.

 

I would like to create a virtual Table with the following step and the 3rd step failed:

 

var Price_Index1 = ADDCOLUMNS('Price Index',"Quarters_from_now",related('Price Index Quarters'[Quarters from now]))
var Price_Index2 = SUMMARIZE(Price_Index1,'Price Index'[Material No], [Quarters_from_now], 'Price Index'[Quarter Year], 'Price Index'[Price],'Price Index'[Qty])
var Price_Index3 = CALCULATETABLE(Price_Index2, Quarters_from_now>3)
return Price_Index3

 

What did I wrong?

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Florian_Kluge , Try with these change , a new table

     

    var Price_Index1 = ADDCOLUMNS('Price Index',"Quarters_from_now",related('Price Index Quarters'[Quarters from now]))
    var Price_Index2 = SUMMARIZE(Price_Index1,Price_Index1[Material No], Price_Index1[Quarters_from_now], Price_Index1[Quarter Year], Price_Index1[Price],Price_Index1[Qty])
    var Price_Index3 = CALCULATETABLE(Price_Index2, Price_Index2[Quarters_from_now]>3)
    return Price_Index3

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Florian_Kluge , Try with these change , a new table

     

    var Price_Index1 = ADDCOLUMNS('Price Index',"Quarters_from_now",related('Price Index Quarters'[Quarters from now]))
    var Price_Index2 = SUMMARIZE(Price_Index1,Price_Index1[Material No], Price_Index1[Quarters_from_now], Price_Index1[Quarter Year], Price_Index1[Price],Price_Index1[Qty])
    var Price_Index3 = CALCULATETABLE(Price_Index2, Price_Index2[Quarters_from_now]>3)
    return Price_Index3

    • Florian_Kluge's avatar
      Florian_Kluge
      Regular Visitor
      4 years ago

      amitchandak: Thanks a lot, now I see it more a matter of the AutoCompletion, which does not present the column names of virtual tables

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Does this work?

    New Table =
SUMMARIZE (
    FILTER ( 'Price Index', 'Price Index Quarters'[Quarters from now] > 3 ),
    'Price Index'[Material No],
    'Price Index Quarters'[Quarters from now],
    'Price Index'[Quarter Year],
    'Price Index'[Price],
    'Price Index'[Qty]
)
    • Florian_Kluge's avatar
      Florian_Kluge
      Regular Visitor
      4 years ago

      Hi PaulDBrown : Thanks a lot, this also helps if I put the 

      'Price Index Quarters'[Quarters from now]  in the related function.

      Thanks