Funciones CUBO-LAMBDA y MDX

LLevaba unas semanas con ganas de probar la compatibilidad de dos de mis categorías de funciones preferidas: LAMBDA y CUBO (y MDX impícito), así que me puse a jugar con ambas, acabando con una agradable sensación...

Si te interesa este tema te recomiendo al menos leas estos tres artículos del blog:
- un primer que habla entre otras cosas de la función MATRIZATEXTO, y cómo nos puede facilitar obtener las cadenas de MDX.
- un segundo y tercer que hablan sobre funciones en MDX como Exists y NonEmpty
NonEmpty
NonEmpty

Vamos a aplicar sobre un ejemplo concreto, donde a partir de una tabla con información, cargada al modelo de datos en PowerPivot, y donde hemos añadido una Medida:
Sum_Total:=SUM([Total])
Y que tenemos por objetivo:
1-Listar nombres de clientes que cumplan las condiciones dadas (triple criterio: País + Producto + Comercial)
2-Obtener el dato de los importes correspondientes.

Lo vemos en la imagen siguiente:

Vemos la base de datos cargada en el modelo de datos (tabla en azul llamada TblVENTAS), y en negro las tres tablas que servirán para perfilar la búsqueda de criterios (TblPAIS, TblPDTO y TblCOMERCIAL respectivamente).
Fíjate que he añadido algunos elementos inexistentes en la base de datos!!... ya que parte del trabajo evitará fallos a este respecto.

La primera parte consistirá en generar tres cadenas de MDX para que conjunto de criterios (país, producto y comerciales), Una vez obtenida la cadena de texto la incluiremos dentro de la función CONJUNTOCUBO correspondiente.
Por tanto, escribiremos en K9:

=LET(VbUSCADO;TblPAIS[Elige paises];
           txtMDX;"[TblVENTAS].[País].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[País];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

De forma similar para los productos, en K11:

=LET(VbUSCADO;TblPDTO[Elige producto];
           txtMDX;"[TblVENTAS].[Producto].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[Producto];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

Acabamos en K13 para los comerciales:

=LET(VbUSCADO;TblCOMERCIAL[Elige comercial];
           txtMDX;"[TblVENTAS].[Comercial].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[Comercial];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

Revisa los enlaces del principio del artículo para entender qué se conseguirá con estas cadenas de texto MDX.
Basicamente lista los elementos seleccionados en las tablas de condiciones..., pero ligadas a la TblVENTAS.
Igualmente fíjate cómo evitamos el fallo para los elementos inexistentes.. un simple SI condicional con ESERROR.

Si incluimos estas cadenas de MDX dentro de la función CONJUNTOCUBO le aportamos un significada de segmentación de la base de datos...
Así en L10 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$9;"paises")
que formará el conjunto de datos de los países elegidos...
En L12 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$11;"pdtos")
que formará el conjunto de datos de los prodcutos elegidos...
Y en L14 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$13;"comerciales")
que formará el conjunto de datos de los comerciales elegidos...

De momento tres subconjuntos de datos de la tabla de ventas...

Construiremos un subconjunto como intersección de los tres anteriores que nos devuelva los elementos del campo 'Cliente' que corresponda.
En K17,empleando la función MDX NonEmpty, escribimos:

=CONJUNTOCUBO("ThisWorkbookDataModel";
"NONEMPTY( [TblVENTAS].[Cliente].children, (" & $K$9 & "," & K11 &","& K13 &") )";
"clientes")

Esta función retornará los clientes, no vacíos, que cumplan al mismo tiempo las tres condiciones de los conjuntos anteriores...
Lo que podemos ver en forma de lista desbordada en K19:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";K17;SECUENCIA(RECUENTOCONJUNTOCUBO(K17)))
la función SECUENCIA se encarga de desbordar los diferentes miembros del conjunto anterior de clientes...

Primera parte lograda!!.

La segunda consiste en saber para cada cliente listado cuántos euros suman, obviamente solo de aquellos registros que cumplan los criterios dados.
Entonces en L19 y trabajando sobre los conjuntos y subconjuntos anteriores escribimos:

=LET(numEltos;RECUENTOCONJUNTOCUBO(K17);
        BYROW(SECUENCIA(numEltos);
              LAMBDA(rw;
                     VALORCUBO("ThisWorkbookDataModel"; INDICE($K$19#;rw);$L$10;$L$12;$L$14;"[Measures].[Sum_Total]"))
                       )
          )

donde la función BYROW se encarga de recorrer los diferentes clientes listados, para ir aplicando a cada uno (A CADA MIEMBRO) la función VALORCUBO que devolverá el dato de la medida generada en el modelo de datos (recuerda los primeros pasos!!) cuando se cumplan los tres criterios: país, producto y comercial.

Conclusión... buenas migas entre unas y otras (funciones LAMBDA y CUBO), y nada que reclamar 👍