Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |