Get Semantic Model data in Dataflow

While I independently discovered this technique, I found later that there was someone else who discovered this approach way earlier than me.

Have you ever tried using dataset (semantic model) as data source for dataflows in Power BI? Chances are high that you did wonder about it and couldn't find a way around.

Well, it is certainly possible and it works wonders just like any other dataflow using more traditional data sources like sharepoint, sqldbs etc.

Prerequisite:

The workspace for the semantic model must be in premium capacity for Data Discovery mode (i.e. Model Navigation). For non-premium workspace, DAX/MDX query does work.
While it is not required, I would highly recommend using DAX Studio for query preparations for both premium and non-premium workspace semantic models.

Step 1: Retrieve dataset server settings

Identify the connection string for the dataset you are trying to use as data source. You can find this info under Server settings for the dataset in settings section.

your connection string would look like something below:

Data Source=powerbi://api.powerbi.com/v1.0/myorg/{Workspace name};Initial Catalog={Datset name};

For the next exercise, you will need following variables:

Server: powerbi://api.powerbi.com/v1.0/myorg/{Workspace name}

Database: {Datset name}

Step 2 (Optional but Recommended): Prepare DAX Query

Semantic models or dataset are tabular models that depends on relationships. In DAX Studio or Query builder applications, you can define filters, parameters and other options which unfortunately is not available in the navigation pane for table selection in dataflow. So it's best to prepare the DAX query beforehand using DAX Studio or other similar application. For this demonstration, I have prepared the following DAX Query:

/* START QUERY BUILDER */

EVALUATE

SUMMARIZECOLUMNS(

'Date'[Year],

'Date'[Month],

'GL LedgerAccountMapping'[FIN report line E],

KEEPFILTERS( TREATAS( {2024}, 'Date'[Year] )),

"Amount", [Amount]

)

ORDER BY

'Date'[Year] ASC,

'Date'[Month] ASC,

'GL LedgerAccountMapping'[FIN report line E] ASC

/* END QUERY BUILDER */

Step 3: Create Dataflow

In the Get Data pop-up window sreach for 'Azure Analysis Services'.

Background: When I was doing some reserach pbi backups in DevOps git, I came to realize that since power bi datasets are hosted in Azure Analysis services, maybe, just maybe it would be possible to access data for dataflow this way.

Enter you Server, Database and DAX query. Use Organization account for Authentication Kind.

And Viola!, you have your table in dataflow. You can set refresh sechdule and create other dataflows and datasets based on this dataflow.

Reach out to me for any questions here: nsfdec14@gmail.com

Page updated

Google Sites

Report abuse