Combine date information different tables Direct query

[ilana105]

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

[v-ljerr-msft · Accepted 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

[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?

Regards

[Greg_Deckler]

Can you post the formula of your measure?

[ilana105]

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

[parry2k]

What is Inventory Coloumn? Is it a calculated measure? Can you share the formulat for inventory column? 

[ilana105]

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:

[parry2k]

To clarify, you want cummulative total for inventory?