October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
Dear community,
I am importing several tables with Direct Query correctly and after that I want to create a table that shows per month the aggregated information from two different tables. Here is a snapshot of what I got:
And the result I want to obtain it's a table combining both data. The problem is that when I add the measure from the first table, it only shows the complete aggregate
In order to solve this I have tried different solutions:
Could you please tell me other solution, or a way to work any of that solutions?
Thank you very much
Solved! Go to Solution.
Hi @ilana105,
In order to solve this I have tried different solutions:
- Create an intermediate table with all dates and use it to link both tables. However, I can´t create a table in Power BI when importing directly with direct query
What data source are you using with direct query model? Is it possible for you to create the Intermediate Calendar table on the data source side(like in a SQL DB)?
In order to solve this I have tried different solutions:
- Calculate the information from the second table using the "Calculate" function, but this doesn´t work.
Have you tried using a similar formula like below to create a measure to calculate aggregate value from another table? It should work in this scenario.
SumOfInventory = CALCULATE ( SUM ( Table2[Inventory] ), FILTER ( Table2, Table2[Year] = MAX ( Table1[Year] ) && Table2[Month] = MAX ( Table1[Month] ) ) )
Regards
Hi @ilana105,
In order to solve this I have tried different solutions:
- Create an intermediate table with all dates and use it to link both tables. However, I can´t create a table in Power BI when importing directly with direct query
What data source are you using with direct query model? Is it possible for you to create the Intermediate Calendar table on the data source side(like in a SQL DB)?
In order to solve this I have tried different solutions:
- Calculate the information from the second table using the "Calculate" function, but this doesn´t work.
Have you tried using a similar formula like below to create a measure to calculate aggregate value from another table? It should work in this scenario.
SumOfInventory = CALCULATE ( SUM ( Table2[Inventory] ), FILTER ( Table2, Table2[Year] = MAX ( Table1[Year] ) && Table2[Month] = MAX ( Table1[Month] ) ) )
Regards
Hello @v-ljerr-msft
Finally I have created an intermediate calendar table and everything is linked through that table
Hi @ilana105,
Great to hear the problem got resolved! Could you accept your last reply as solution to help others who has similar issue easily find the answer and close this thread?
Regards
Can you post the formula of your measure?
Hello @Greg_Deckler
this is the structure I have in the table:
The 3 values are direct values from different tables in the database. the first 2 come from one table and the Inventory from other table. I want to aggregate the 3 values by month
What is Inventory Coloumn? Is it a calculated measure? Can you share the formulat for inventory column?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello @parry2k
Inventory is not a calculated measure. It is a number extracted from the database, and I want to show the aggregate per month. Please find attached the settings of the value:
To clarify, you want cummulative total for inventory?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
103 | |
98 | |
96 | |
78 | |
41 |
User | Count |
---|---|
139 | |
137 | |
126 | |
99 | |
62 |