Adding DAX expression when connecting to a dataset

[molegris]

Hi,

I was wondering if I can add a DAX expression when I connect to a Power BI semantic model (Dataset). 

Kind of the same way when you connect to Analysys Services.

When connecting to AS, you have the option to add a DAX expression.	When connecting to Power BI semantic models, we can't.

Usecase :

I'm creating a report for upper management in which we present high level aggregated KPIs from 5 different datasets.  

Right now I'm adding tables from all 5 datasets into the report (with DirectQuery) then I create aggregated tables with DAX expressions to evaluate the high-level KPIs.  At the end, only the 5 aggregated tables are used in the report.

It works but it's a huge mess! There's 100s of tables and measures, many with duplicated names!  I'm concerned about maintenance and ease of use for future people that would have to work on it... actually, even myself in a year from now!

I think it would be so much cleaner if I could submit the 5 DAX expresssions directly in the connections to each dataset so the report would only show the imported aggregated tables?

Thank you.

--mo

[v-yilong-msft]

Hi @molegris ,

First of all it's true that you can't directly use DAX expressions in the semantic model in Power BI Service like you can in Power BI Desktop, but I think you can navigate the semantic model features that allow you to create and manage different data items such as calculated groups, pivots, and roles: Semantic models in the Power BI service - Power BI | Microsoft Learn

Secondly you also mentioned that there are a lot of duplicate tables and aggregated values in your dataset, so I think you could consider using DirectQuery and Analysis Services for Power BI datasets to combine multiple semantic models, and also utilize the Aggregate functionality in Power BI to cache the data at the aggregate level, which could also help you reduce duplicate data.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[molegris]

Hi @v-yilong-msft ,

Thanks for your reply.

I know I can manage items such as calculated groups, pivots, and roles; but it's not useful for what I'm trying to acheive.

You suggested to use the Aggregate functionality; but it's not available for tables coming from a dataset. It seems to be a freature only available or imported tables.  My connections to datasets are in DirectQuery mode and even if I wanted to change the storage mode, it's grayed out.

Aggregation feature not available on tables from datasets

Finaly, you suggested using DirectQuery and Analysis Services for Power BI datasets to combine multiple semantic models. How would do that?  I'm confused about the How and Where exactly I could do it.  Can you point out, on the diagram below, where you would do it and how (which tool/fontionnality)?

My flow diagram

Thanks you

--mo