Date Table With Direct Query

[thegekko42]

Hi

I have 2 direct queries from an sql server with no option to make changes in the source data setup.

One is an order query and the other an invoice query.

I need to have a link between them in terms of date/month, but there is no date table in the source data.

I am aiming for this:

So comparing what has been ordered vs what has been invoiced per month and quarter.

But I can't find a way to achieve this with direct query.

I was tipped to the Composite Models, and I found that it solved the problem perfectly as I simply added this table and linked it to my queries

But the Composite Models are only in preview so I need to find a different solution.

How do I replicate the functionality of a date/month table without actually adding the table?

Is there a way around the problem while keeping it as direct query?

[v-shex-msft]

HI @thegekko42,

If you use 'Composite Models', you need to switch to import then you can publish your report with 'many to many' relationship.

If you not want to use that feature, you need to create a bridge table with unique value to link both tables, but this also can't achieve in directquery mode. (calculate table has been disabled in directquery mode)

Regards,

Xiaoxin Sheng

[thegekko42]

Thank you for your reply Xiaoxin Sheng

Unfortunately I need to run on direct query, as this will need to be Live data.

Is there a measure solution or something like that which can help me bridge the two queries?

[v-shex-msft]

Hi @thegekko42,

You can consider to create this table on database side.

Regards,

Xiaoxin Sheng

[thegekko42]

Thank you for the suggestion, but I can't touch the source data setup.

For now I will keep a master of the model in Composite and then have an import copy for publishing.

It doesn't give me everything I need, but it will have to do until Composite goes Live in October.