Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

[tessahurr]

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.

Here are some areas that we'd like to hear about in particular:

• Performance
• Query editor experience--the remote model query doesn't show up in the query editor and only in the data source settings dialog. What are your thoughts?
• Navigator experience
• Thoughts around governance and permissions for models that leverage this feature
• Nesting models, i.e. building a composite model on top of a composite model
• Automatic page refresh for live connect in composite models

Thanks and we look forward to hearing your feedback!

- The Power BI Modeling Team

[jeroenterheerdt]

sounds like a permission problem or credentials not being configured for the source. Have you checked the source settings in the Service?

[AlejandroErazo]

I'm having trouble developing the composite models.
I connect to a powerbi service dataset and create a model in direct query.

I then enabled DirectQuery for PowerBi datasets and the AS option.

Despite this, it doesn't give me the option to connect to any other file.

Also, I went to External Tools and tried to open the Tabular Editor and it gives me an error: The remote server returned an error: (401) Unauthorized

Am I doing something wrong?
Do I need to enable anything else for composite models to work?

[jeroenterheerdt]

not sure. Did you restart Desktop after enabling the preview?

[AlejandroErazo]

Yes, I did. But nothing happens even though I am connected live to the published dataset.

[jeroenterheerdt]

is this dataset published on Pro/Premium/PPU? Is it not located in "my workspace"?

[AlejandroErazo]

The report is published with a free account, I do not have a pro or premium.
Also, it is published on my workspace.

[jeroenterheerdt]

from the documentation: "

• Using DirectQuery on datasets from “My workspace” isn't currently supported.": https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

[AlejandroErazo]

With another workspace, it just worked, thank you very much

[jeroenterheerdt]

ok great!

[onli]

Hello 

This feature could support SQL Server Analysis Services 2022 now. It's great !

Composite models on SQL Server Analysis Services | Microsoft Power BI Blog | Microsoft Power BI

Is it possible to support SQL Server Analysis Services 2019 in the future ?

Thank you.

[jeroenterheerdt]

no, we are not planning to add support for older versions of SSAS than 2022, since it requires changes in the engine itself.

[onli]

Thank you for the reply.
But it's a pity because SSAS 2019 is the latest official version for now. 

The on-premises users have to wait next year at least to use this fonctionality. There is also a migration to do.

Anyway it's a very useful fonctionality. Thank you for the work.

[samaguire]

So, this one is an oddball one.

When I query a particular DQ dataset form Excel I get the error "Data seen at different points in time during execution of this query are inconsistent with each other. This could be due to external transactions updating same data while this query was running. Please rerun this query." However, the exact same setup querying the underlying dataset (the source for the DQ dataset, which is a typical import model) doesn't produce this error. 🤔

Tracing using Dax Studio, I find the underlying MDX query (which works when directly queried against the source dataset) is:

MDX Query:

SELECT 
    NON EMPTY 
        Hierarchize
        (
            AddCalculatedMembers
            (
                {DrillDownLevel({[Value Selector].[Value Selector].[All]})}
            )
        )
    DIMENSION PROPERTIES 
        PARENT_UNIQUE_NAME,
        HIERARCHY_UNIQUE_NAME
     ON COLUMNS
FROM [Model]
WHERE 
    [Measures].[Total Actuals NZD]
CELL PROPERTIES 
    VALUE,
    FORMAT_STRING,
    LANGUAGE,
    BACK_COLOR,
    FORE_COLOR,
    FONT_FLAGS;

Resultant DAX generated to the source dataset is (and when run in DAX Studio is working):

DAX Query 1:

EVALUATE
GROUPCROSSAPPLY(
    "__Agg0", [Total Actuals NZD]
)

DAX Query 2:

DEFINE
VAR _Var0 = VALUES('Value Selector'[Value Selector])
EVALUATE
GROUPCROSSAPPLY(
    'Value Selector'[Value Selector],
    ALLSELECTEDREMOVE(_Var0),
    "__Agg0", [Total Actuals NZD]
)

DAX Query 3:

EVALUATE
SUMMARIZE(
    VALUES('Value Selector'),
    'Value Selector'[Value Selector],
    'Value Selector'[Order]
)

The model structure is simple enough - although not the best modelling setup. The measure used is a simple column sum...

Not sure where to go from here? 🤷‍

[jeroenterheerdt]

hmm, can you please open an issue on issues.powerbi.com or open a support ticket?

[samaguire]

In case you want to track your end: Support request ID 2205310030002149

[hemd1]

When you link with second dataset, the RLS would disappear. It would have data loss risk. Would there be any plan to fix that?

[jeroenterheerdt]

I am not sure I follow this - can you please provide more details?

[Ismaelhb1986]

I am referring to the fact that currently with PRO licensing it is necessary to release construction access to the sets that I use in my panels that use the resource

[gerardcanals]

Hi,

I have an error in all the reports with this functionality. All the visuals of the reports are broken:

The error is:

I have admin permissions to all datasets. The reports are connected to two datasets one in the same workspace of the report and another to other workspace. In both workspaces I am admin.

Do you now what is happening? Should I contact the support?

[soldous]

We have the same issue in our tenant. In PBI Desktop all works fine but in the Service there is the same issue. I'm the admin in both workspaces as well. So far haven't found any solution. 

[gerardcanals]

In my case in power PI desktop doesn't works too.