Forum Discussion
DAX Query to MDX Cube
- 10 years ago
Found my issue. When I was trying to use calculated measures that referenced outside the date range eg. Year on Year comparison, then would not work if I used attributes of my date dimension. I needed to change the calculated measures to use the Key on the Date dimension. The problem I have is I'm using integer data types through my FACT tables which don't scale in Power BI visuals. I just have to convert to use the 4 byte DATE data type and all will be good.
My Date dimension is based off a view in the database table. One of the columns in the view is [Days from Today] which performs a DATEDIFF function to give each date in my dimension table an integer value. Eg. yesterday = 1, the day before =2 etc. This technique seems to provide a nice sliding date-range approach so users can simply refresh their reports/dashboards to see recent data without having to fiddle with filters. I run a "ProcessUpdate" on my date dimension at the start of each day.
In Power BI, I like to set it to be "less than 31". I capture the DAXQuery and run it using DAXStudio locally. It seems to kill any data older than the filter setting. I would have thought a Scoped measure in the MDX cube would still return a value but it won't
I've tried using LookupCube, LinkMember, StrToMember etc but it's like the session is scoping the cube down to a sub-cube so will never return a value.
Does this make sense?
The MDX Calculated measure is as follows:
CREATE MEMBER CURRENTCUBE.[Measures].[Purchases Previous Year] AS NULL,
FORMAT_STRING = "#,###";
SCOPE ([Purchases Previous Year]);
SCOPE([Dates].[Year - Week - Day].[Year]);
this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(1),[Purchases]),NULL);
END SCOPE;
SCOPE([Dates].[Year - Week - Day].[Week]);
this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(52),[Purchases]),NULL);
END SCOPE;
SCOPE([Dates].[Year - Week - Day].[Day]);
this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(52*7),[Purchases]),NULL);
END SCOPE;
format_string(this) = "#,###";
END SCOPE;
Built a cut down MDX cube and finding this is also happening when I try to use the [Days from Today] directly.
The backup solution is to use Dynamic named sets of date ranges. However Power BI doesn't allow you to use Named Sets when building reports.
- Phil_Seamark10 years agoMicrosoft Employee
Found my issue. When I was trying to use calculated measures that referenced outside the date range eg. Year on Year comparison, then would not work if I used attributes of my date dimension. I needed to change the calculated measures to use the Key on the Date dimension. The problem I have is I'm using integer data types through my FACT tables which don't scale in Power BI visuals. I just have to convert to use the 4 byte DATE data type and all will be good.
- mattkarriker9 years agoHelper I
Phil
Can you expand a little more on your solutin to this issue? I am experienceing the exact same issue and have been looking at this for 3 days now. When you say you needed to have the Calculated Measures use the Key of the Date Dimension are you refering to the Scope statements for [Purchases Previous Year]? Are you saying other than using expressions like [Dates].[Year - Week - Day].currentmember.lag(1) you are using [Dates].[Key].members?