cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Comparing measures in different ranges using Live Connection Mode to SSAS

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

3 REPLIES 3
MFelix
Super User
Super User

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.

MFelix_0-1610800167501.png

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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:
print slicer DateKey.PNG

 

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

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors