The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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.
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.
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.
User | Count |
---|---|
20 | |
17 | |
15 | |
14 | |
12 |
User | Count |
---|---|
34 | |
34 | |
20 | |
19 | |
18 |