cancel
Showing results for
Did you mean:
Helper III

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

This table has an ID and for that ID the annual savings numbers and when the first benefit date would be.

I need to create a new calculated table. This table will get the ID and Savings/year information form the above table. However, it needs to create one row per ID and years between 2013-2023. So for every ID number, there would be a row with a year between 2013 and 2023. It will look like below:

 ID Year Saving/year Calculated Savings 1 2013 \$5 Calculate the savings for the year 1 2012 \$5 Calculate the savings for the year 1 2014 \$5 Calculate the savings for the year 1 2015 \$5 Calculate the savings for the year 2 2013 \$3 Calculate the savings for the year 2 2012 \$3 Calculate the savings for the year 2 2014 \$3 Calculate the savings for the year 2 2015 \$3 Calculate the savings for the year 3 2013 \$15 Calculate the savings for the year 3 2012 \$15 Calculate the savings for the year 3 2014 \$15 Calculate the savings for the year 3 2015 \$15 Calculate the savings for the year 4 2013 \$20 Calculate the savings for the year 4 2012 \$20 Calculate the savings for the year 4 2014 \$20 Calculate the savings for the year 4 2015 \$20 Calculate the savings for the year 5 2013 \$27 Calculate the savings for the year 5 2012 \$27 Calculate the savings for the year 5 2014 \$27 Calculate the savings for the year 5 2015 \$27 Calculate the savings for the year

Any suggestions on how I can do this. Initially, I've created one table with all IDs and for each calendar year savings I've made a calculated column. But because the years were in the columns, I wasn't able to create a relationship. I need to have both the IDs and years in rows as above so I can create a relationship with their columns.

1 ACCEPTED SOLUTION
Super User

So, something like this?

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

See attached.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

So, something like this?

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

See attached.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

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.

Announcements

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors