cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numersoz
Helper III
Helper III

Calculated Table from Another Table Info

Hi,

I have a table with below information on Power BI:

IDBenefit DateSaving/year
11/1/2013$5
23/5/2014$3
34/6/2014$15
45/5/2015$20
58/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:

 

IDYearSaving/yearCalculated Savings
12013$5Calculate the savings for the year
12012$5Calculate the savings for the year
12014$5Calculate the savings for the year
12015$5Calculate the savings for the year
22013$3Calculate the savings for the year
22012$3Calculate the savings for the year
22014$3Calculate the savings for the year
22015$3Calculate the savings for the year
32013$15Calculate the savings for the year
32012$15Calculate the savings for the year
32014$15Calculate the savings for the year
32015$15Calculate the savings for the year
42013$20Calculate the savings for the year
42012$20Calculate the savings for the year
42014$20Calculate the savings for the year
42015$20Calculate the savings for the year
52013$27Calculate the savings for the year
52012$27Calculate the savings for the year
52014$27Calculate the savings for the year
52015$27Calculate 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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors