Forum Discussion
Helper ICombine date information different tables Direct query
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:
- One table with income data ("importeVenta")
- Other table with the number of impressions
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:
- 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
- I have created a new column in both tables called "monthYear", where I have the month and year of every line in the database. However, there are multiple values of the same monthYear in each table, so can´t make a N-M relationships
- Calculate the information from the second table using the "Calculate" function, but this doesn´t work.
Could you please tell me other solution, or a way to work any of that solutions?
Thank you very much
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)?:smileyhappy:
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.:smileyhappy:
SumOfInventory = CALCULATE ( SUM ( Table2[Inventory] ), FILTER ( Table2, Table2[Year] = MAX ( Table1[Year] ) && Table2[Month] = MAX ( Table1[Month] ) ) )Regards
8 Replies
- Greg_Deckler
Community Champion
Can you post the formula of your measure?
ilana105Hello 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
- parry2k
Super User
What is Inventory Coloumn? Is it a calculated measure? Can you share the formulat for inventory column?
v-ljerr-msftMicrosoft Employee
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)?:smileyhappy:
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.:smileyhappy:
SumOfInventory = CALCULATE ( SUM ( Table2[Inventory] ), FILTER ( Table2, Table2[Year] = MAX ( Table1[Year] ) && Table2[Month] = MAX ( Table1[Month] ) ) )Regards
- ilana105
Hello v-ljerr-msft
Finally I have created an intermediate calendar table and everything is linked through that table
- v-ljerr-msft
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?:smileyhappy:
Regards
