sql-serverpowerbissasmdxpowerbi-desktop

How to take MDX Query from SSAS Multidimensional Cube and Use To Build Power BI Report?

I'm struggling to recreate a report in Power BI. The old report is on a Sharepoint site and was built using MS Performance Poit.

I'm using Power BI Desktop with 'Live Connection' to our SSAS Multidimensional Cube to try and re-create this report.

In SSMS I have the following MDX query which gives me the exact results that I am looking for: I can't paste the code nor can I upload a screenshot due to company restrictions, but the gist of it is...

ON COLUMNS I have a set of multiple LASTPERIODS() functions to return the needed value of various MEASURES ON ROWS for a series of relative dates.

Example:

SELECT 
  {
      --  Last 5 Days (9  December 2023, 10 December 2023, etc)
      LASTPERIODS(5, [Yearly Review].[ALL].LastChild.Lastchild.LastChild.LastChild.LastChild.LAG(1))
      -- Current Month (December 2023)
      LASTPERIODS(1, [Yearly Review].[All].LastChild.LastChild.LastChild.LastChild)
      ... And so on...
  } ON COLUMNS,

  {
      [Measures].[someMeasure1],
      [Measures].[someMeasure2],
      ... And so on....
  }

This MDX query in SSMS displays the results exactly how I want to display in the report.

How can I Display this in Power BI? As I'm using Live Connection, I must do this in the CubeWhen I connect to the database Power BI only displays Calculated members and Dimension tables. Is it possible to build a Dimension based on this query?

I've tried using the Import method and pasting in the MDX query, but [when the data is imported into Power BI, &the column nas are the result of the LASTPERIODS function [](Dimensi Nameon]Hier.archy Name.[Level].[Member]. These columns are supposed to be dynamic, so I'm unsure how to "rename" the columns without making them static.

I'm very confused.... Any help is appreciated.

Solution

  • Power BI Desktop is a DAX client, so only sends DAX queries. You could use a Paginated Report with ReportBuilder and reuse that MDX query.