# Function CUBESET with EXISTS inside



## frederic (Dec 21, 2014)

Hello everybody,

I'm using a lot cube formulas to build my reports and now i try to insert mdx functions inside to improve powerfull.
These functions are always used against a PowerPivot model (tabular).
CUBESET with TOPCOUNT, FILTER... are working well.
I also use CUBESET with EXISTS and it works well with 2 sets but as soon as i try to insert a measure as third parameter i get a #N/A error.

For example this is working well.
*=CUBESET("ThisWorkbookDataModel","EXISTS([Dates].[Mois].CHILDREN,[Dates].[Année].&[2014])","What is displayed in cell")*
But if i try to add a measure from my model ( [Measures].[Total CA HT] ) i get #N/A error, here's the example. I want the same result but only for those who have amounts for [Total CA HT].
*=CUBESET("ThisWorkbookDataModel","EXISTS([Dates].[Mois].CHILDREN,[Dates].[Année].&[2014],[Measures].[Total CA HT])","What is displayed in cell")*
The measure *[Total CA HT] =sum(Base[Montant HT])*
The sets are not hierarchies.

I can't understand the problem.
Is it a syntax problem ? In MDX help about EXISTS Measures are between quotes "Internet Sales"
Should i use the name of a field instead of measure ? (i don't think so but...)
Maybe this parameter is not supported with tabular model like the excel cube function CUBEMEMBERPROPERTY ?

I thank you in advance for you're answer.

Just another thing, is somebody knows what are the MDX functions that we could use with excel cube formulas against a PowerPivot model ?

Thank you.

PS : Excuse me for my poor English.


----------



## scottsen (Dec 22, 2014)

I have summoned Chris Webb.


----------



## frederic (Dec 23, 2014)

Thank you.


----------



## ChrisWebb (Dec 23, 2014)

I suspect the Exists() function won't work with PowerPivot because all measures are calculated measures. Can you try using the Nonempty() function instead?

Chris


----------



## frederic (Dec 24, 2014)

Thank you for you're answer Chris, i've bought you're Power Query Book and it's very usefull :wink:.  Everyday i check you're blog to get new informations. The post you did  about how to get all excel files in a folder with a parametrized path is  so usefull. The most difficult thing in that matter is to prompt user for parameter and to get these parameters directly from database as all possible values (ex all years in the database). It partially works with Power Query and now i'm searching how to get it work directly with power pivot (not with stored procedures).

EXISTS and NONEMPTY are two different functions. EXISTS  can return a set of the matching members of two expressions and it can  also check if there are values for a specified measure.
And the thing  i've wanted to understand is how to add a measure as third parameter. I  suppose it's a simple problem of syntax but i can't deal with it. Maybe it's the simple fact of integrate quotes in the function ? Like this "'" or like this """ or ?

I've  tried the NONEMPTY function to filter a set with empty members and it's  not working, there is no error but the set contains empty values (if i  check the empty value error in Excel with this simple formula cell="" i  get TRUE). Then i've used the EXCEPT function with .&[] as parameter  and it works great. Maybe it's possible to use the FILTER function.  This function works with a measure but i can't get it to work with a set  with expression like this [Dates].[Month].Children <> "".

A  lot of MDX functions which return a member or a set are working with  cube formulas but the ones who require an expression between quotes are  hard to get working.

Thank you.


----------



## ChrisWebb (Dec 24, 2014)

Hi Frederic,

Sorry for not being able to give a properly detailed reply last time - let me explain myself properly now.

The third parameter of the Exists() function does not take the name of a measure, it takes the name of a measure group. A measure group is a concept that doesn't make much sense in Power Pivot or SSAS Tabular (it does in SSAS Multidimensional though) - however, Power Pivot does understand what measure groups are. You can see the measure groups in a model by querying the MDSCHEMA_MEASUREGROUPS schema rowset like so:

select * from $system.mdschema_measuregroups

This shows that for Power Pivot the name of a measure group is the name of a table. Therefore, for you, in the third parameter of the Exists() function you need to use the name of a table in your model. Something like this:

exists(
[DimDate].[CalendarYear].[CalendarYear].members,
{[DimProduct].[Color].&[NA]},
"FactInternetSales")

The reason that I suggested to use the NonEmpty() function is that, when I'm writing MDX, this is the function I use most often when filtering out empty values from a set - I can't remember the last time I used the Exists() function with the third parameter, and although there are some subtle differences between the two functions in many scenarios they are interchangeable. It sounds like you need to use NonEmpty() here because you want to filter out empty values by a measure value. Can you show me how you were trying to use NonEmpty()?

Chris


----------



## frederic (Dec 26, 2014)

Thank you Chris for you're answer and to take time to explain.

I want to build reports based on cube formulas to be able to apply more formatting options and to avoid pivot table limitations.
To  do that i wanted to check if some dimensions exists and have values to  avoid displaying dimensions for nothing. For examples i want to show only  subcategories that have sales and displaying years that have sales and  are not empty. I have a linked table for dates and i can't understand  why i have empty values whereas nothing is empty in my source table ?

For example i try to count customers (clients in french) that are in fact table for every year with that formula :
=COUNTSET(CUBESET("ThisWorkbookDataModel","EXISTS([Base].[N°  Client].[N° Client].Members,[Dates].[Année].&["&D55&"])"))
D55 is a cell with the year. Whatever the year is i get always the same number of customers ?

Moreover,  for empty values, i succeeded to get no error with EXISTS by  referencing a cell that contains the name of the table between quotes as  you suggested.
But as i said NONEMPTY and EXISTS function returns no  error but empty values are not filtered ? The only way i succed is with  except .&[]. Why ?

Maybe i could upload my workbook somewhere to show you the context or send it to you by mail. It's 3,7 Mb file.

Thank you again.


----------



## ChrisWebb (Dec 28, 2014)

It might be a good idea if you email me the workbook - you can find my contact details at Crossjoin Consulting.

Chris


----------

