Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors