Miguel Escobar on 28 Oct 2015 07:43:41
Something new came out just a few weeks ago. We now have the ability to connect LIVE to a SQL Database and use Power Query to create our Query that will later be used in our Power BI Desktop. This is simply AMAZING!! this uses the new SSAS Tabular engine in Direct Query mode, but we can't add Measures, Calculated Fields or Calculated Tables...yet.
This should be a fairly simple request. Please let us add our measures so we can create the Model that we need in real time :)
Administrator on 16 Nov 2016 01:36:58
Hi everyone. This has actually been available for a while, sorry we never updated the Idea! You can write some DAX functions even in Direct Query mode. The functions that are supported vary by data source, but Intellisense will 'prompts' you with only the valid options are you enter your formulas. Please note it's not the same as creating measures over connection to Analysis Services models - we're still working on that, you can add support for that here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11685723-create-calculated-measures-on-top-of-a-live-ssas-c Thanks for voting!
- Comments (33)
RE: Create DAX measures, fields and tables in Direct Query mode
"Function 'IF' is not allowed as part of calculated column DAX expressions on DirectQuery models." - seriously? I want to calculate something without affecting the SQL server source.
Same when trying "FORMAT", for example: FORMAT(WEEKNUM(KPI_TAT_DATA[First_ACK],21),"00")
RE: Create DAX measures, fields and tables in Direct Query mode
'@Gijs – it’s been available since earlier this year. If you upgrade to the latest version you’ll see it enabled (though read my other comments below)
@Jonathan – One thing I should have pointed out: as per powerbi.microsoft.com/en-us/documentation/p.., we do limit the DAX functions by default but you can allow unrestricted DAX measures under File -> Options and settings -> Options -> DirectQuery. There’s a potential performance impact because you’re pushing all those calculations back down to the source. It’s also why we don’t allow DAX tables – the performance implications are typically too great. But: it's on our backlog too. Most users only use measures and columns so we shipped this feature to them now rather than waiting for all the DAX table support too.
@Anton – Sorry if this sounds condescending but please make sure you’re connecting to a SQL relational database using DirectQuery and not to SSAS using ‘Live Connect’ mode. I’ve had a couple of other people get in touch who’re confused by the difference. Our documentation could be clearer on this! SSAS Live Connect is covered here powerbi.microsoft.com/en-us/documentation/p... At the moment you can’t create measures/columns/tables against that either, but we’re working on it. You can add support for that one here ideas.powerbi.com/forums/265200-power-bi-id..
RE: Create DAX measures, fields and tables in Direct Query mode
Since which version?
RE: Create DAX measures, fields and tables in Direct Query mode
"This has actually been available for a while" - did you actually read the suggestion? "DAX measures, fields and tables in DirectQuery", not "a few DAX functions".
RE: Create DAX measures, fields and tables in Direct Query mode
This is the most rediculous response that I've ever seen. I don't see any option to add a measure or a table when I'm connected to a SQL server but the request is marked as complted. I think this is what appens when people start show too much love. Very dissapointed!
RE: Create DAX measures, fields and tables in Direct Query mode
But you cant add a DAX query table in Direct query mode. When in direct query mode for instance there is NO way to create a date table using DAX
RE: Create DAX measures, fields and tables in Direct Query mode
Not Completed: but we can't add Measures, Calculated Fields or Calculated Tables...yet.
or Calculated Tables is not yet available so this is incomplete.
RE: Create DAX measures, fields and tables in Direct Query mode
This is really urgent for Bizzy.co.id, can you make this to TOP PRORITITY.. thank you.
RE: Create DAX measures, fields and tables in Direct Query mode
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/
Thank's it already TOP-N in new power BI. :) Cool
RE: Create DAX measures, fields and tables in Direct Query mode
Calculated Fields and Measures must be needed when we go for Connect LIVE to a SQL Database. Please, Include this feature.