DirectQuery - create variances?

[Anonymous]

Hi everyone,

I am pretty new to Power BI Desktop and just started exploring DirectQuery.  We have very large data tables so Import doesn't seem like a practical option to use, however, it looks like I cannot create variances with DirectQuery.  How would I show year-over-year variances if I linked directly to our SQL server db?  (I would normally create the time-intelligence 'groups' (ex.  TTM/YTD curr yr and TTM/YTD prior Yr via calculated measures and then create a third calculated measure to generate the variance between the two.  However, this is not an option in DirectQuery). 

What do the rest of you who are referencing databases do - create some type of import instead?  Isn't that very time-consuming?

Thank you very much in advance.

[Seth_C_Bauer]

@Anonymous This is a known issue with DirectQuery. There is no capability currently to create measures or calculated columns... I use tabular models for all large scale models where I can build whatever I need, then direct connect to that from Power BI.

Importing will not work either with large data sets as you are capped at 250mb for your entire PBIX file... 

[Anonymous]

Hi Eno1978,

What do you mean when you say that you use 'tabular models for all large scale models'? 

Thanks,

[Anonymous]

...just adding onto my follow-up question - what do you use to build the tabular model?

Thanks...

[Seth_C_Bauer · Accepted solution]

@Anonymous Tabular models are a SSAS (SQL Server Analysis Services) solution by Microsoft. Details here

Tabular models require BI or Enterprise SQL editions and are built in SSDT (SQL Server Data Tools) in Visual Studio.

The same technology is behind the Power BI Desktop, but there are currently limitations on file size etc.

Using an SSAS Tabular model solution offers the greatest scalability and flexibility within Power BI. For large scale datasets it is the only complete modeling solution for Power BI at the moment.