CUBE formula to find name based on initials

[Bassehave]

Hej everyone,

I'm in need of some help with a CUBE formula.

I have two sheets:
Goal and Oversigt

In the sheet "Oversigt" cell A2 I have the initials of a person - in this example we could say it was "JOJOH".
In my Goal sheet, I need the formula to look into my data model and find the initials but it should return the name of the person instead.
So in this case, it should return "John Johnson".

I got a similar function to work when creating measures with numbers, but I can't get it to return a single value.
My formula looks like this:

=CUBEMEMBER("ThisWorkbookDataModel";"[Medarbejdere1].[Initialer].&["&Oversigt!$A2&"]")

Medarbejdere1 is the datamodel data for all our co-workers and the column "Initialer" is the column with the lookup value (JOJOH). I need it to return the value in the column called "Navn" that matches the value in column "Initialer".

Currently my formula is returning the value from initials, so basically the value is the exact same as the value in Oversigt A2. I have tried to change the formula to
=CUBEMEMBER("ThisWorkbookDataModel";"[Medarbejdere1].[Navn].&["&Oversigt!$A2&"]")
But it still does not work.


Can you help me?


Thanks in advance
B

[Bob Phillips]

I blogged about this 5 years ago , https://wessexbi.wordpress.com/2014/...th-powerpivot/

You need some MDX in your CUBEMEMBER function

Please Login or Register  to view this content.

If you coud get multiple children, i.e. people with same initials, you need to add a CUBESET and CUBERANKEDMEMBER function into the mix.

[Bassehave]

Hej Bob,

Thank you so much for the formula. At first, I couldn't get it to work, since you use the english keys - and changing the keys didn't work.
When trying one by one, and keeping the key "comma" after Children it now works!

You are awesome!


Final formula: =CUBEMEMBER("ThisWorkbookDataModel";"EXISTS([Medarbejdere1].[Navn].Children,[Medarbejdere1].[Initialer].&["&Oversigt!$A$2&"])")

Thanks
B

[Bob Phillips]

Sorry about that B, I know that in Denmark you use semi-colons as separators not commas as we do. As you saw, and as Imke Feldman noted in a comment to my blog post, even if you do use semi-colons, the MDX part has to use commas, presumably the MDX authors didn't include localisation.

BTW, I think it is useful to keep the IFERROR, just in case A2 contains an invalid set of initials, maybe add a meaningful error

=IFERROR(CUBEMEMBER("ThisWorkbookDataModel";"EXISTS([Medarbejdere1].[Navn].Children,[Medarbejdere1].[Initialer].&["&Oversigt!$A$2&"])"),"Initials not found")

localised for Denmark of course.