Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.