Function CUBESET with EXISTS inside

[frederic]

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]

I have summoned Chris Webb.

 

[frederic]

Thank you.

 

[ChrisWebb]

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]

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]

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]

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]

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

Chris