141 questions
0
votes
1
answer
16
views
MDX Returning Null Values Even With Non Empty
Good afternoon. Have an MDX query still returning null values even though I have non empty in the MDX. This query does run and it does return values where I would expect to see them. Trying to get rid ...
-2
votes
0
answers
22
views
Unable to extract UDA tagged to Account using MDX script in Essbase 21C
We are currently using Essbase 21C and trying to extract metadata using MDX script. Along with Metadata, we are also trying to extract UDA.
In the Essbase, Account1 is tagged with only one UDA which ...
- 63
0
votes
0
answers
21
views
MDX is not returning result
I have a mdx query which i taking forever to run i am new to MDX and not sure what is wrong with this query and why results are not coming ?
Here is my query
SELECT NON EMPTY Hierarchize({...
0
votes
0
answers
19
views
Can I take a velocity figure for a Fix Version in eazyBI JIRA App and use it calculate the velocity of a subset Fix Version? e.g. beta 1, beta 2, etc
In our JIRA, we have a Fix Version(FV) 1 that covers everything in Release 1. For this overarching Release 1 we have a velocity that is an average of the last 5 sprints.
We also have FVs 1a, 1b and 1c ...
- 49
0
votes
0
answers
29
views
Define MDX Update Allocation Method - Excel What-If Calculate Change
I have setup up a multi-dimensional data model in SSAS which is embedded in Microsoft Excel as a Pivot Table. In Excel I want to use the OLAP what-If analysis functionalities.
Screenshort Excel ...
0
votes
1
answer
73
views
I am using MDX queries for a few days and I have this confusion
Can anyone explain what is the difference in using filter() and Where in MDX queries?
I am trying to create MDX queries using the OLAP4j query model and I am confused as to when to use filter() and ...
0
votes
1
answer
426
views
How to include multiple items in "SAP VARIABLES"?
I'm new to developing queries in BW MDX. And I have a question that seems simple, but I can't find any documentation/solution anywhere.
I'd like to know how to add multiple "filters" for the ...
- 33
0
votes
1
answer
41
views
MDX equivalent Query for the below DAX CALCULATE query
I am trying to build a Measure using MDX that is equivalent to the following Power BI DAX calculation:
CALCULATE(
[Total Orders],
'Calendar Dimension'[LAST_WEEK_FLAG]=“1"
)
0
votes
1
answer
33
views
How can I show the last opening period for each project in my project dimension hierarchy using MDX ( Tail function)?
I have two attributes hierarchies in my dimension [Dim Project]:
Project Code
Opening date
Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03
Project B with 2 opening date ...
1
vote
1
answer
32
views
I need to calculate 2nd year retention for Employees
We have an EmployeeEvents cube (multidimensional). I need to get a set of employees that started anytime in the last 24 months (I have a new hire flag). The count of this set is the denominator. ...
- 13
0
votes
1
answer
1k
views
INNER JOIN between 2 $SYSTEM.TMSCHEMA_ tables using MDXQuery
I'm doing some queries in a SSAS model using SSMS and MDXQuery . I'm able to run a single select on any table as below:
SELECT[TableID], [Name] FROM $SYSTEM.TMSCHEMA_PARTITIONS
SELECT [ID], [Name] ...
- 33
0
votes
1
answer
50
views
how to get previous value in a fact table for a measure in mdx
I want to write an mdx query with a calculated Measure.
Here is my fact table:
How to implement a mesaure with the rule below :
If CurrentAtt != PreviousAtt then PreviousValue
else CurrentValue
Thank ...
1
vote
1
answer
24
views
MDX parameters in SSRS with large data set
I have a lot of data in a cube and I have a query that works perfectly.
I need to make this smaller and the user needs to add the Number and Year and the data displayed because there is just too much ...
- 11
1
vote
1
answer
74
views
Unknown error of multidimensional expressions (MDX) calculation in SSAS Cube
I just got a project handover regarding SSAS cubes and multidimensional expressions. There is a calculation written in Visual Studio:
(StrToMember(
"[DIM Time_V3].[Year - Week - Date].[Month].&...
- 11
1
vote
1
answer
70
views
How to add one more measure in MDX where clause?
Below query is perfectly working and bringing rolling 12 months data for selected measure. Now how do I add one more measure to same MDX query so that query fetch rolling 12 months for 2 measures?
...
- 11
1
vote
1
answer
107
views
MDX Comparing Dates (Before and After)
Hoping for any help with hopefully a simple problem - I'm writing MDX into a rule builder within a company application. Here is the expression:
IIF(Time.CurrentMember >= [Time].&[456], "...
- 11
0
votes
1
answer
84
views
MDX Add Condition to filter for the first 6 month of the year
I would like to find the top 10 most profitable customers in the first six months of the year 2021 (January through June) sorted by profit in descending order.
The following filter for all criteria ...
- 640
1
vote
1
answer
257
views
UNION ALL of 2 MDX queries (within data cube)
I'm mdx rookie..
A box of gold for whoever can tell how to do UNION ALL of these two MDX queries:
SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[...
- 25
5
votes
0
answers
141
views
Find Number of times a particular character appears in a string using MDX query
In the Microsoft SSAS, I have a dimension column that contains multiple values separated by the special character | in a single row as below.
Example value of a row: Image|Video|Audio|
Requirement:
So,...
- 91
0
votes
0
answers
131
views
How to compare year to date figures from the current year with the previous year?
With the bellow MDX query im attempting to return a comparasion of sales, but its not working
For example i want to compare the values from current year until today (29/september/2022) with the values ...
0
votes
1
answer
418
views
MDX - Query Attribute in the CrossJoin - Essbase
I currently have a CrossJoin working for UDA. However we want to change to using Attribute value instead. Not sure how to get the Syntax correct to use Attribute in the CrossJoin in Essbase. I ...
2
votes
1
answer
457
views
How to use multi level hierarchy names in MDX query
I need to use the dimension field under the 2 hierarchies i.e., one hierarchy under another hierarchy in the MDX query.
Am having the below hierarchies/data in my cube:
I need to use the highlighted [...
- 225
2
votes
0
answers
275
views
How to compare the current member value with previous member value in mdx query?
I am having the following data in my SSAS cube
I need to get the value based on a comparison between the current value and the previous value using MDX query.
In this example data, I need to get the ...
- 225
4
votes
0
answers
338
views
Type mismatch error occurs while casting string values to date type in MDX query with some blank rows
I have a string-type dimension in my SSAS cube having the date values along with some blank values and please find the screenshot for your reference.
I am trying to convert these values into Date ...
- 225
5
votes
1
answer
311
views
Multiple dimensions conditions in SSAS MDX query not working
I Am having the following data in my SSAS cube.
My need is to get the value of the measure based on two conditions with two different dimensions using the MDX.
In this example data, I need to get ...
- 225
1
vote
2
answers
204
views
Sort measures where row and column axis are cross-joined in MDX
I've a table where there's two dimensions in the row axis crossjoined and two dimensions plus a measure crossjoined in the column axis. Eg:
I would like to sort the rows of a specific column, taking ...
0
votes
1
answer
139
views
Generate MDX-Query
I have a cube with several dimensions and measures.
Now, I want to generate a query with all articles [Artikel].[Barcode] combine with colors [Artikel].[Farbe], sizes [Artikel].[Grösse] and warehouses ...
- 6,249
0
votes
0
answers
31
views
Why am I getting the same output from different codes?
So, I've been learning MDX and I came across some basic data querying along multiple dimensions.
The concept of tuples and sets are kind of clear for me.
What's the query supposed to do: show a ...
- 1
5
votes
1
answer
245
views
IIF() function not working properly while using multiple times in single MDX query
I need to get the aggregated values based on two conditions using single MDX query
Am having below data
I have created two calculated functions using IIF() function in a below query
WITH
...
- 225
3
votes
0
answers
204
views
IIF() function not properly working while using in MDX query
I need to get calculated measure values based on conditions.
Am having the below data in my cube
I have created below calculated measure using IIF() function
IF([Title] = "Sales Representative&...
- 225
4
votes
1
answer
472
views
How to sort records in MDX query when numeric expression is used with TopCount function
I am trying to sort the Top 201 records in my SSAS cube either ascending or descending based on requirements in my C# code.
Here I a using Adventure Works Database, where [Reseller Order Count] is ...
- 91
2
votes
0
answers
89
views
MDX Query using generate function and calculated members
I've been trying to solve this query for a while but I don't understand where I'm wrong.
The questions of the query is this one:
Show the winners having a total winner rank points greater than the ...
- 35
1
vote
0
answers
79
views
How to use date range in IIF function in MDX Query
Am having 2001,2002 and 2003 years of data in the SSAS server.
Without using where clause and filters I need to get the aggregated data using the IIF function in the MDX query for a particular date ...
- 225
1
vote
0
answers
36
views
Unable to get period over period data from SSAS server using mdx query
Am using the MDX query in the SSAS server.
I want to get the current year(01/01/2002 - 31/12/2002) and previous year(01/01/2001 - 31/12/2001) data for a specific date range(dynamically change) in a ...
- 225
1
vote
0
answers
29
views
MDX - sum on related stores
help with MDX query
please
i need to
calculated stock pcs
for current member
as sum of SAME region
and only for distribution centers
for example
first row Object = 'WH_1'
Region = 'A'
i need to get ...
- 51
2
votes
0
answers
143
views
The Measures hierarchy already appears in the Axis0 axis
I want to use the measures fields in both rows and columns in the MDX query and I have used the below query
SELECT{[Measures].[Average Rate]} ON COLUMNS,
NONEMPTY(
(
TOPCOUNT(
(
...
- 225
2
votes
1
answer
86
views
To calculate the average of Latest 12 weeks
I figured out how to calculate the average of latest 12 weeks volume in my data. But lets say the current year does not have 12 weeks, it only has 8 weeks then I want to take the remaining 4 weeks ...
- 39
0
votes
1
answer
117
views
How to use the equivalent SQL with sub query and where clause in SSAS MDX Cube to create a measure?
I'm trying to "convert" a SQL query to SSAS MDX expression to create a measure in SSAS Multidimensional existent cube.
In SQL I have the table below:
Product
ProductCode
OperatorName
Step
...
- 157
2
votes
1
answer
1k
views
MDX - How to present members of hierarchy as multiple columns
I am new to MDX on Analysis Services. So I have a bit of a problem. How do I get max value of child values in a parent?
For example, I am using AdventureWorksDW on SSAS. The model has a hierarchy in ...
- 477
1
vote
1
answer
161
views
trouble with dynamic year filter in MDX
I have the following query that I use in Azure data factory(this is on the source of a copy action):
SELECT
{ [Measures].[0INV_QTY],
[Measures].[0NET_VAL_S] }
ON COLUMNS,
NON EMPTY
{ [...
- 13
1
vote
0
answers
100
views
Converting MDX query into SSAS cube expression [Calculations]
How can I go around to amending this MDX query
WITH MEMBER [Measures].[PanelSoldMonthsPre]
AS ' [In Charge Date].[In Charge Date Days in Month].CURRENTMEMBER.membervalue'
MEMBER [Measures]....
- 369
0
votes
1
answer
143
views
Simple division in MDX
Trying to achieve the PanelSoldMonths rate using PanelSoldDays and DateDaysInMonth. However, I am getting a return of nulls when doing this simple division.
PanelSoldMonths = PanelSoldDays / ...
- 369
0
votes
1
answer
615
views
Using a date range in calculated member in MDX query for Tabular cube model
I have a requirement where I have to get the values of different measure with each measure having its own date range, the following query works and gets me the data over an year,
WITH MEMBER [...
- 495
0
votes
1
answer
263
views
MDX Hierarchy Dimension to Column
I have a cube having a dimension using hierarchy. This is the example of pivot cube from Excel using pivot level
This data are attached as one hierarchy drill down from Excel pivot
When I translated ...
- 551
1
vote
1
answer
372
views
How to resolve "Type Mismatch error" in MDX query
I am struggling with the Type Mismatch error when I query the cube in SSAS.
The MDX query is shown below:
WITH
MEMBER [MEASURES].[TopNFilter] AS
IIF
(
Count((EXISTING ...
- 121
0
votes
1
answer
115
views
How to Rank in order without skipping in MDX query
I have a MDX query that Rank clients but in the WHERE Clause I added
With Member [Measures].[Rank2] As
IIF([DimClient].[Client].CurrentMember.member_caption = [ClientBenchmark].[ClientGroup]....
- 121
0
votes
1
answer
335
views
How to remove nulls value from a returned result set of multiple dimensions in MDX Query when Ranking is used
I want to RANK Customer by Volume and AvgCost in MDX query. However, when I use the query by below:
WITH
SET TiedRanksbyVol AS
ORDER( [Dim Customer].[Customer Category].[Customer Category].Members, [...
- 121
0
votes
1
answer
183
views
How to get distinct values of Year level in MDX?
I have a cube Work with two dimensions :
Dimension Date with 3 levels : Day, month and year.
Dimension Task with 2 levels : Task A and Task B.
I want to show tasks (A and B) by year, the problem is ...
- 1
0
votes
1
answer
774
views
How to extract month, year and date from Date level in MDX
It is my first time working with MDX in JPivot, I have a Date level (Format: YYYY-MM-dd) and I have to do some queries based on the year or month of this date. I did not find a way to extract this ...
- 1
1
vote
1
answer
158
views
MDX - Top N elements of every subgroup
Let's assume I have a cube with a sales fact table and 3 dimensions: Time (with hierarchy year-month-day), Geography (with hierarchy continent-country-region) and Product with some characteristics ...
- 35