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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
keobrie
Helper I
Helper I

MDX Expression as Card Field in SSAS Live Connection based on Slicer criteria

I have a multi-page PowerBI report with a Live Connection to an SSAS Multidimensional Cube. 

 

On one of the pages I have a monthly run chart (stacked bar chart) whose scope is governed by a slicer.  In the typical use case, the user selects a time period in the slicer that ends before the last period in the SSAS cube (due in large part to the need to remove data in runout periods).

 

On the same report tab, I have a Card with a value.  The value is a calculated value stored in the SSAS cube and it is reporting based on the entire period defined by the slicer.

 

The challenge is that the slicer typically presents 12-24 months but the card needs to report a more current value.  We have determined that we want the card to report the average of the last three months for the slicer period but we want the stacked bar chart to report the entire selected period. 

 

I have a series of MDX expressions that calculate the proper 3 month value but when the card reports the value, it does not use the last period in the slicer, it appears to use what Excel Pivot tables treat as the "Grand Total" value.

 

My question is twofold:

"Can I use the last value in the slicer to govern the Card Field value?"

or

"Can I use an MDX expression in the Card Field that in turn uses the last value of the slicer to govern the value in the Card Field value"

 

Of course, if there is a better way, I'm open to suggestions.

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

Hi @keobrie ,


"Can I use an MDX expression in the Card Field that in turn uses the last value of the slicer to govern the value in the Card Field value"


We cna create two measure for each visual, one uses the full period and another uses the current three months, for the measure of current months, we can refer to the following query:

CREATE MEMBER CURRENTCUBE.[Measures].[current previous 3]  as
SUM
(
   [Date].[Calendar Date].CurrentMember.Lag(3) : 
   [Date].[Calendar Date].CurrentMember.lag(1), 
   [Measures].[Internet Sales Count]
);

If you have other problem related to SSAS, I would suggest you opening a case in SSAS MSDN forum at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

Best Regards,

Teige

View solution in original post

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

Hi @keobrie ,


"Can I use an MDX expression in the Card Field that in turn uses the last value of the slicer to govern the value in the Card Field value"


We cna create two measure for each visual, one uses the full period and another uses the current three months, for the measure of current months, we can refer to the following query:

CREATE MEMBER CURRENTCUBE.[Measures].[current previous 3]  as
SUM
(
   [Date].[Calendar Date].CurrentMember.Lag(3) : 
   [Date].[Calendar Date].CurrentMember.lag(1), 
   [Measures].[Internet Sales Count]
);

If you have other problem related to SSAS, I would suggest you opening a case in SSAS MSDN forum at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

Best Regards,

Teige

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors