Forum Discussion

numersoz's avatar
numersoz
Helper III
6 years ago
Solved

Calculated Table from Another Table Info

Hi, I have a table with below information on Power BI: ID Benefit Date Saving/year 1 1/1/2013 $5 2 3/5/2014 $3 3 4/6/2014 $15 4 5/5/2015 $20 5 8/8/2015 $27 T...
calculated column
calculated table
DAX
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    So, something like this?

     

    Table3 = 
VAR __ids = DISTINCT(SELECTCOLUMNS('Table2',"__ID",[ID]))
VAR __years = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(Table2,"__Date",[Benefit Date]),"__Year",YEAR([__Date])),"__Year",[__Year]))
VAR __table = GENERATEALL(__ids,__years)
VAR __table1 = ADDCOLUMNS(__table,"__Savings/year",MAXX(FILTER('Table2',[ID] = [__ID]),[Saving/year]))
RETURN __table1
See attached.

    See attached.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

So, something like this?

 

Table3 = 
VAR __ids = DISTINCT(SELECTCOLUMNS('Table2',"__ID",[ID]))
VAR __years = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(Table2,"__Date",[Benefit Date]),"__Year",YEAR([__Date])),"__Year",[__Year]))
VAR __table = GENERATEALL(__ids,__years)
VAR __table1 = ADDCOLUMNS(__table,"__Savings/year",MAXX(FILTER('Table2',[ID] = [__ID]),[Saving/year]))
RETURN __table1
See attached.

See attached.

numersoz's avatar
numersoz
Helper III
6 years ago

Thank you very much this worked! However, I face another problem now with a circular reference.  Can there be anything in the DAX code that can cause this? 

The table I've just created with your code was a savings table.  I have another table with spendings. These two tables are non in a direct relationship, I have other tables in between.

In order for me to have a line and stacked column chart, my idea was to use an auto-generated dates table that would have a relationship with each of these tables individually. However, this results in a circular reference. And without this relationship, I can't create this graph where they have a common date.