Comparing measures in different ranges using Live Connection Mode to SSAS

[Anonymous]

Hi,

My company is doing a proof of concept with PowerBI ( Today we use SyncFusion that accept MDX queries directly ). And we have a problem with data comparison. Ex: 'MeasureX was 33% greater with Range2 than Range1'. We don't know how do that in PowerBI. In our actual BI software we do MDX queries in the source, but PowerBI don't accept MDX queries with LiveConnection and we wish LiveConnection.

In MDX what we get:

member MesRange1 as
aggregate ( Date.Date.Range1Begin : Date:Date:Range1End, MeasureX )
member MesRange2 as
aggregate ( Date.Date.Range2Begin : Date:Date:Range2End, MeasureX )
member Percent as
(MesRange2 - MesRange1 ) / MesRange1, format_string = 'percent'
select
{ MesRange1, MesRange2, Percent }
from MyCube
where User.MyUser

How we can do that with the LiveConnection? Is it possible to enable MDX in LiveConnection? Or is necessary other approach?

Best Regards,

Luis

[MFelix]

Hi @Anonymous ,

How are you selecting the two date ranges for your measure?

Depending on the way this is done you need to have something similar to this one:

Delta X Measure =
tt =
VAR mesrange1 =
    CALCULATE (
        [MEASUREX],
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( 'Calendar'[Date] ),
            MAX ( 'Calendar'[Date] )
        )
    )
VAR mesrange2 =
    CALCULATE (
        [MEASUREX],
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( 'Calendar'[Date] ) - 365,
            MAX ( 'Calendar'[Date] ) - 365
        )
    )
RETURN
     ( mesrange2 - mesrange1 ) / mesrange1

In the example I present the calculation is done using a slicer for the current year and then I take 365 to the maximum and minimum date.

If you can tell me how you are filtering the date for both periods is just a matter of adjustment of the second part of the calculate for each mesrange variables.

[Anonymous]

Hi @MFelix ,

Thank You for your help. About your suggestion: I am not using SSAS Tabular, I am using SSAS Multidimensional 2014, so I think that this dax will not work, I think that is necessary a MDX calculated member.

"How are you selecting the two date ranges for your measure?"

About that:

'Range2' the user will choose in a slicer in PowerBI:

Range1: Will be counted the number of days in Range2, and the Range1 will end one day before the beginning of Range2, and will begin depending of the count of days of range2. Ex:

User choose '2021/01/11 - 2021/01/15' for range2 in PowerBI Slicer
The range1 will be somehow '2021/01/06 - 2020/01/10'.

Best Regards,
Luis

[MFelix]

Hi @Anonymous ,

To my knowldge you can add measures to SSAS using DAX have you tried adding any measure to your model?

Anyhow try the following measure if possible:

Delta X Measure =


VAR mesrange1 =
    CALCULATE (
        [MEASUREX],
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( 'Calendar'[Date] ) -1,
            MIN ( 'Calendar'[Date] ) - DATEDIFF(MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ), DAY)
        )
    )
VAR mesrange2 =
    CALCULATE (
        [MEASUREX],
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( 'Calendar'[Date] ),
            MAX ( 'Calendar'[Date] )
        )
    )
RETURN
     ( mesrange2 - mesrange1 ) / mesrange1

This may need some adjustments.