Funciones CUBO: Contando y listando distintos

Profundifaremos en esta ocasión en las funciones CUBO, sobre un Modelo de datos en Power Pivot, para obtener un recuento de elementos distintos.
Además con algo de MDX y expresiones como Exists o NonEmpty conseguiremos incluso listar dichos elementos distintos...

Comenzaremos agregando al Modelo de datos nuestra 'Tabla1'. Y como siempre (puedes revisar posts previos sobre el tema) añadiremos una Tabla de Calendario... la cual relacionaremos a través del campo Fecha.
Veamos nuestra tabla origen:

Vemos rápidamente que tenemos datos de los dos primeros trimestres del 2020 (Q1 y Q2), con ventas a distintos paises, donde cada factura puede tener un número de líneas diferente (1,2,3, ...).
Nos interesa conocer el número de facturas únicas por cada trimestre, cliente y país.

Lo primero que haremos, dentro del Modelo de datos, de la tabla Calendario creada, será añadir una columna calculada que nos informará del trimestre al que pertenece cada fecha, con la fórmula:
="Q"&ROUNDUP(MONTH([Date])/3; 0)

Por otra parte incluiremos una medida en el modelo:
DistinctCount_NFra:=DISTINCTCOUNT([Num Fra])
donde empleamos la función DAX llamada DISTINCTCOUNT que recuenta el número de elementos distintos según los filtros aplicados...

Estamos listos para volver a la hoja de cálculo...
Para verificaciones posteriores generaremos una tabla dinámica desde el Modelo de datos.

Para replicar los resultados de esa tabla dinámica que nos muestra un recuento de elementos distintos, esto es, el número de facturas diferentes por cliente, país y trimestre, crearemos tres conjuntos distintos con CONJUNTOCUBO o MIEMBROCUBO o MIEMBRORANGOCUBO.

Entonces en L12 añadimos:
=MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[País].[All].[ES]")
en M12:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";"[Calendario].[Trimestre].[All].Children";1)
en N12 para el segundo elemento de los Trimestres:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";"[Calendario].[Trimestre].[All].Children";2)
y en L13 para recuperar la lista de clientes únicos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";
"[Tabla1].[Cliente].Children";
SECUENCIA(RECUENTOCONJUNTOCUBO(CONJUNTOCUBO("ThisWorkbookDataModel";"[Tabla1].[Cliente].Children"))))
Con esta distribución de miembros de los distintos CUBOS implicados, podemos usar VALORCUBO para retornar los conteos de distintas facturas...
En M13:N16 incorporamos la función:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[DistinctCount_NFra]";$L13;M$12;$L$12)
donde llamamos a nuestra 'Medida' del modelo de datos, solicitando el cruce adecuado por los tres criterios previos: cliente, trimestre y país.

Interesante... pero no muy novedoso :(

Lo interesante viene ahora... ¿y si quiero conocer los elementos que componen esos conteos?, es decir, y si deseo obtener el listado de facturas distinas que corresponden al Q1, del cliente DOS, S.L. y además del país ES!!??.

Serán las expresiones MDX dentro de la función CONJUNTOCUBO las que nos ayudará a conseguirlo...
Vayamos paso a paso limitando la lista de facturas distintas...
Nuestro primer conjunto serán las facturas distintas, de todo cliente, país o trimestre.
En la celda L19 añadimos la fórmula:
=CONJUNTOCUBO("ThisWorkbookDataModel";"([Tabla1].[Num Fra].Children)";"NumFras")
que noso retorna un listado de quince números de facturas distintas...

Necesitamos más precisión... así que en un segundo paso añadimos un criterio asociado al país desado: ES.
En la celda M19 insertamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"Exists([Tabla1].[Num Fra].[All].Children, [Tabla1].[País].[ES] )";"NumFra_ES")
donde la función Exists devuelve los elementos coincidentes en ambos conjuntos (siempre que estén dedntro de la misma dimensión o tabla para nosotros)...
Tendremos en este caso siete números de factura de ES diferentes...

Siguente etapa. Incorporamos un nuevo criterio... además de cruzar con país:ES, indicamos que sea el cliente: DOS, S.L.
Al estar el campo cliente en la misma dimensión/tabla, podemos seguir empleando la función Exists. En la celda N19 formulamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"Exists([Tabla1].[Num Fra].[All].Children, ([Tabla1].[País].[ES], [Tabla1].[Cliente].[DOS,S.L.]) )";
"NumFra_ES_DOS")
Importante colocar el doble criterio País y Cliente entre paréntesis...
con esta fórmula nos quedaríamos con cuatro número de factura diferentes... ya queda poco ;-)

Última fase. Algo más compleja, ya que el último criterio a aplicar, el de Trimestre, pertenece a otra dimensión o tabla :O
Lo que significa que Exists no nos ayudará. Debemos emplear la función NonEmpty, que nos permite trabajar sobre elementos de distintas dimensiones!!.
Por tanto la fúnción buscada, que nos devuelve el listado de facturas distintas, para ES, cliente DOS,SL y trimestre Q1, la añadimos en la celda O19:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"NonEmpty([Tabla1].[Num Fra].Children, ([Measures].[DistinctCount_NFra], [Tabla1].[País].[ES], [Tabla1].[Cliente].[DOS,S.L.] , [Calendario].[Trimestre].[Q1]) )";
"NumFra_ES_DOS_Q1")
NonEmpty requiere que en el segundo argumento añadamos un 'conjunto', el cual debe incluir una medida, seguido de cuantos criterios se requieran; independientemente de su dimensión de procedencia.
El conjunto creado está compuesto por las tres facturas distintas que buscamos...

Solo nos queda listarlas con la función MIEMBRORANGOCUBO en la celda O22:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$O$19;SECUENCIA(RECUENTOCONJUNTOCUBO($O$19)))

Conseguido!!