topic Re: Quering a SSAS with DAX with folders/subcubes in DAX Commands and Tips

Quering a SSAS with DAX with folders/subcubes

Jimmy801 — Fri, 27 Nov 2020 11:54:43 GMT

Hello Experts

I'm wondering if somebody can give me some guidline how to do or if its even possible to query a SSAS with power query with a DAX-statement (not MDX). I tried to use DAX-studio to give it a try, and a simple DAX-query like Evaluate 'Employee' works just fine. (see on the left side up, also a subcube is specified - ITRS... hours)

the thing I would like to achieve is to use a DAX-query in the power query connector for SSAS

and it's not working. I get this error

What I've noticed is that we have in our database "OLAP_BI" whe have some folders/subcubes and in the PBI connector I can only define a Database. So how can I possible define a subcube with DAX? like I'm doing in MDX with FROM [Project]?

Any help is really appreciated

Jimmy

Re: Quering a SSAS with DAX with folders/subcubes

AntrikshSharma — Sat, 28 Nov 2020 08:13:51 GMT

Jimmy801 If I understand correctly you are trying to query MDX cube with DAX, right? I don't think that's possible, you can use MDX to query DAX but not the other way around.

With DAX I can get easily data from a SSAS Tabular Model into Power query:

Re: Quering a SSAS with DAX with folders/subcubes

Jimmy801 — Mon, 30 Nov 2020 06:11:59 GMT

Hello AntrikshSharma

many thanks for your answer. I cannot tell you if I have a MDX-cube. But even if so... I'm able to query it with DAX-Studio where this statement gives me the table Employee

I can navigate on the left to the database OLAP_BI and can choose the subcube ITRS418 Hours.

But on Power Query my only parameter is the Database OLAP_BI

in MDX you would write FROM [ITRS418 Hours]. Is there any way to query this cube with DAX?

Jimmy

Re: Quering a SSAS with DAX with folders/subcubes

AntrikshSharma — Mon, 30 Nov 2020 07:47:34 GMT

Jimmy801 Yup, I have Tabular model deployed, I no experience with MDX. Why don't you write MDX script in the input box? I think it works in DAX studio because Excel generates MDX code, so it could be working only for compatibility and ease of use. But anything other than that may not have an idea of EVALUATE syntax.

The Basic MDX Query (MDX) | Microsoft Docs

Re: Quering a SSAS with DAX with folders/subcubes

Jimmy801 — Mon, 30 Nov 2020 08:26:32 GMT

Hello AntrikshSharma

I already wrote. But as sometimes I have performance issues and because I've some more experience in DAX, I would prefer to write the query in DAX. But it seems no way to achieve this in my situation. What a pity

Jimmy