This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 26%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
I have a measure [Measures].[Value]
and dimensions:
[Company]
[Month]
I need to create a new measure that would NOT contain the following set of data:
Excluding Company A and months 1 and 2
Excluding Company B and months 2 and 3
I will create a new measure in Excel, there are Olap Tools and an editor for the new measure.
Can anybody help me to write it in MDX? A polite request for help
Thanks in advance
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
You probably won't believe the amount of hassle these requirements actually cause.
You'll have to create two sets, one of company.company.company and the other of month.month.month, making sure to check the 'recalculate...' checkbox. If you don't, many filtering operations on any of the dimensions will ruin your result.
The measure in its most generic form will be
aggregate ( existing ( company_set * month_set - (company.company.A, month.month.1) - .... - (company.company.B, month.month.3) ) , measures.value )
And, you'll still have more fine-tuning to do if, for example, Value happens to be a calculated measure.
Excel and MDX are a fun combination :)
Thank You for Your answer
Too difficult for me, what you write.
But I don't give up easily
"to create two sets..."
How to do it, where to do it?
I tried to do it myself by combining IIF functions (with OR and AND operators). But I couldn't.
It's all in Excel as you specified. The Fields, Items, & Sets button is right next to the OLAP Tools button you mentioned.
Note that I inferred the names of the two attribute hierarchies from just the dimension names you supplied, and that you'll have to replace the ellipsis with all the other combinations of companies and months that must be excluded. Good news is that there's a lot of drag-and-drop functionality available in Excel's editors for OLAP constructs.
Thank you for your answers!
I created 2 sets
CompanySet - (rows)
MonthSet - (columns)
I do not have recalcualte button as you mensioned
For the beginning I created new mesure
aggregate ( existing ( CompanySet * MonthSet) ,[Measures].[Value])
and it gives me error
What am I doing wrong?
So far, a lot :) Rows and columns have nothing to do with the solution I proposed - those words weren't even used. 'Recalculate...' is a checkbox, 'Recalculate set with every update'. And, "ARG" doesn't look like the full error message from either SSAS or Excel.
Interesting. Thank you for Your help, give some time to check it
As far as I'm concerned, you have all the time in the world :)
Slowly from the beginning
I did the simplest test:
1) I created a data set. Named it [Set]
I can see the set is working, I can put it in both rows and columns. It gives the value on the "Value" measure.
2) I have created a new measure "Value1". In the MDX box I dragged this created set from the left. There is only [Set] in field MDX. I save it.
In the PivotTable, I dumped the "Value" measure and inserted "Value1"
It displays ARG, why?
I think I make error in MDX syntax
aggregate ( existing ( [CompanySet] * [MonthSet]) ,[Measures].[Value])
I get even ARG on this
A set expression is invalid as the code of a calculated measure, and frankly, since you try this, you're way out of your depth with the original problem. There're a lot of basics you need to understand first.
Also, the 'ARG' error message that keeps appearing seems to mean that your OLAP server is not MS SSAS.
Server is SSAS.
I connect to the data via AS
I'm trying to simplify
I'm trying to get any result
Why is this formula not working?
aggregate (existing ([CompanySet] * [MonthSet]), [Measures]. [Value])
This is exactly what you wrote.
I should get the value from these companies and these months as a result. And gets ARG
Thanks for your help, sorry you have to struggle with this :)
I see an extra space in the '[Measures]. [Value]' part. It exceeds SSAS's prodigious tolerance for bad input. Next up for troubleshooting are the entities referred to as [CompanySet] and [MonthSet].
Sorry about that "space". I pasted it wrong. After removing the space, continue to do the same. ARG
I think that this is the problem inside cube.
Could this not be done in another way, such as through IIF
IIF((([Company]=A OR [Company]=B) AND ([Month]=[202201] OR [Month]=[202202]))
," ",[Measures].[Value])
I works fine for the measure, but if I add another condition in [202203], only company B. It crashes
set expression is invalid
I think so
Syntax
Aggregate(Set_Expression [ ,Numeric_Expression ])