Comparing dates using SSAS cube

[MarnikV]

Hi there,

I've been searching for days without finding a solution for this problem, so here it goes:

There's two ways of using a SSAS cube to browse data and create charts: a live connection or by importing the data. The live connection works great, but has some disadvantages. The biggest one for me is that I can't create calculated measures. For example, I want to compare sales of a month in the current year with the month of the previous year. However, since I can't write any DAX queries this seems impossible? It's just one example of the things I can't seem to achieve without creating calculated measures.

So I tried importing the data, which does allow me to create calculated measures. Since I have a big cube this takes a long time to load for starters. But next to that, and more important to me, it doesn't recognize relationships and hierarchies from my cube. Since ssas hides the foreign keys in the fact tables I don't know how to manually assign them either.

So to sum it up: Is it possible to, for example, compare sales over different years without using calculated measures? If not, how can I achieve this functionality for my SSAS cube without losing my relationships?

[v-sihou-msft]

@MarnikV

In this scenario, if you need to keep the relationships, you have to use live connection. Otherwise when you use Import mode, the measures, dimension members will be resolved as data fields  and retrieved into same dataset. Since you can't creating calcualted measure within Power BI Desktop under live connection, I suggest you build those caluclated measures within cube, they can be loaded into Power BI properly. 

Regards,

[ADP007]

In my molap cube I have some calculated measures that return amounts for YearToDate, YearToMonth, YearToDatePreviousYear based on a date hierarchy. The calculated measures are written in mdx.

Example for measure “Sale Net Net Y-1”

AGGREGATE(

    PARALLELPERIOD(

        [Calendar].[Hierarchy].[Year],

        1,

        [Calendar].[Hierarchy].CurrentMember

    )

    , [Measures].[Sale Net Net]

)

These measures work fine in an Excel pivot tables but don’t when using PowerBi slicers

Excel :

Pivot Table (with comments in yellow)

 

PowerBI :

Using the slicers or filters doesn’t work

The new matrix preview visualisation works but is not user friendly

Many thanks in advance

David

[Habib · Accepted solution]

MarnikV - The idea behind live connection for SSAS is that you will not be using DAX features for Powre BI instead all the transformations will be done using MDX for SSAS.

Now you have only choice to create those transformations within your Cube.

[MarnikV]

Hi there,

I've been searching for days without finding a solution for this problem, so here it goes:

There's two ways of using a SSAS cube to browse data and create charts: a live connection or by importing the data. The live connection works great, but has some disadvantages. The biggest one for me is that I can't create calculated measures. For example, I want to compare sales of a month in the current year with the month of the previous year. However, since I can't write any DAX queries this seems impossible? It's just one example of the things I can't seem to achieve without creating calculated measures.

So I tried importing the data, which does allow me to create calculated measures. Since I have a big cube this takes a long time to load for starters. But next to that, and more important to me, it doesn't recognize relationships and hierarchies from my cube. Since ssas hides the foreign keys in the fact tables I don't know how to manually assign them either.

So to sum it up: Is it possible to, for example, compare sales over different years without using calculated measures? If not, how can I achieve this functionality for my SSAS cube without losing my relationships?