DAX: medida filtrada por otra medida con SUMMARECOLUMNS: equivalente de T-SQL GROUP, HAVING,..

[Syndicate_Admin]

Tengo el siguiente escenario:

Tengo una tabla llamada EmployeeTable; tiene 16 empleados distintos, pero cada empleado puede tener más de una fila,
en función del número de trabajos que ha tenido; digamos que George tiene 2 trabajos (pintor, conductor) y, por lo tanto, hay dos filas para él.

(Sin embargo, EmployeeId y EmployeeName están vinculados entre sí; un EmployeeId siempre hace referencia a un EmployeeName solamente)

La columna: EmployeeTable[IsActiveEmployee] determina si un empleado está activo ?

Esta columna solo puede tener valores "Sí" o "No", abreviados por "Y" y "N" respectivamente.

Ahora, el requisito principal:

Desarrollar una medida para obtener la plantilla de empleados ACTIVOS con múltiples trabajos.

1) En primer lugar, identifique a los empleados que tienen el valor "Y" para la columna EmployeeTable[IsActiveEmployee].

2) Luego, filtre aún más esta lista, restringiéndola solo a los empleados con más de un trabajo (distinto).

3) Luego, obtenga un DISTINCTCOUNT de los empleados.

Hay 16 empleados distintos; 11 de ellos están activos; entre estas 11 personas activas, necesito obtener un recuento de personas con más de un trabajo.

Necesito la respuesta como 5, es decir, cinco personas tienen más de un trabajo y siguen activas.

¿Cómo puedo conseguir esto?

¿Alguien puede dar alguna sugerencia?

He detallado todo en este archivo .pbix y archivo de Excel.

[Syndicate_Admin]

Además, quiero mencionar que esta pregunta es un caso de negocio típico del lenguaje T-SQL (base de datos de Microsoft SQL Server); Se puede protipificar para muchas situaciones.

CREATE TABLE #EmployeeTable

(

EmployeeId int,
EmployeeName nvarchar(50),
Job nvarchar(20),
IsActiveEmployee nchar(1)

);




INSERT INTO #EmployeeTable

VALUES

(1,'Mike','Driver','Y'),
(2,'James','Janitor','N'),
(3,'George','Painter','Y'),
(3,'George','Driver','Y'),
(4,'Adam','Driver','N'),
(4,'Adam','Janitor','N'),
(5,'Lisa','Analyst','N'),
(6,'Rebecca','Supervisor','Y'),
(6,'Rebecca','Analyst','Y'),
(6,'Rebecca','Engineer','Y'),
(7,'Kyle','Driver','N'),
(8,'Autumn','Janitor','Y'),
(9,'Jeanne','Doctor','Y'),
(9,'Jeanne','Manager','Y'),
(10,'James','Janitor','Y'),
(10,'James','Painter','N'),
(10,'James','Driver','N'),
(11,'Jack','Janitor','Y'),
(11,'Jack','Painter','Y'),
(11,'Jack','Driver','N'),
(12,'Alex','Painter','Y'),
(12,'Alex','Painter','Y'),
(13,'Jake','Janitor','N'),
(13,'Jake','Painter','N'),
(13,'Jake','Driver','Y'),
(13,'Jake','Driver','Y'),
(14,'Bob','Janitor','N'),
(14,'Bob','Janitor','N'),
(14,'Bob','Painter','Y'),
(14,'Bob','Painter','Y'),
(15,'Melissa','Janitor','N'),
(15,'Melissa','Janitor','N'),
(15,'Melissa','Painter','Y'),
(15,'Melissa','Driver','Y'),
(16,'Rick','Janitor','N'),
(16,'Rick','Painter','N'),
(16,'Rick','Driver','N')

;




SELECT *
FROM #EmployeeTable
ORDER BY EmployeeId

SELECT

EmployeeId,
EmployeeName,

COUNT(DISTINCT Job) AS NumberOfJobsOfActiveEmployees_WithMoreThanOneJob

FROM #EmployeeTable

WHERE IsActiveEmployee = 'Y'

GROUP BY

EmployeeId,
EmployeeName

HAVING COUNT(DISTINCT Job) > 1;

--a list of 5 active employees with more than one job--
--this has to be converted to a measure--
--see below--

SELECT COUNT(*) AS NumberOfActiveEmployeesWithMoreThanOneJob_DAX_Measure

FROM

(

         --this query is the same as above--

         SELECT

         EmployeeId,
         EmployeeName,

         COUNT(DISTINCT Job) AS NumberOfJobsOfActiveEmployees_WithMoreThanOneJob

         FROM #EmployeeTable

         WHERE IsActiveEmployee = 'Y'

         GROUP BY

         EmployeeId,
         EmployeeName

         HAVING COUNT(DISTINCT Job) > 1

         --this query is the same as above--

) x

--the answer here is the value of my DAX measure--

Estoy publicando esto porque otros usuarios que están haciendo la transición de T-SQL a DAX pueden beneficiarse; es decir, cómo usar SUMMARIZECOLUMNS en DAX para obtener una solución equivalente en T-SQL mediante GROUP BY, HAVING y un conjunto de instrucciones SELECT...

Recuerde que solo hay una mesa en la que necesito trabajar: #EmployeeTable; si tiene más de una tabla, debe hacer algunos ajustes menores (aunque no muchos) a la solución que publiqué.

[Syndicate_Admin]

Es posible que estés complicando un poco las cosas.

[Syndicate_Admin]

Gracias @lbendlin

¿Esto también funciona cuando el valor de IsActiveEmployee cambia en todos los trabajos? Digamos, tengo a James y Jack abajo, quiero que Jack esté incluido, pero no James, a pesar de que ambos tienen varios trabajos; Jack ha tenido dos trabajos mientras estaba activo, mientras que James solo ha tenido un trabajo mientras estaba activo.

Ahora, necesito que la respuesta cambie a 4. ¿Es su código DAX una solución infalible para tales cambios?

(Lo sé, no publiqué este escenario antes, porque quería que la pregunta fuera lo más simple posible, para empezar).

[Syndicate_Admin]

Proporcione datos de muestra que cubran completamente su problema.
Muestre el resultado esperado en función de los datos de muestra que proporcionó.

[Syndicate_Admin]

Consulte el archivo de Power BI y el archivo de Excel; todo lo que allí se indica.

[Syndicate_Admin · Accepted solution]

@lbendlin

Gracias por tu idea; Creo que lo conseguí; Lo único es que no incluiste el Trabajo en tu fórmula; en mi proyecto real, tengo un duplicado de los nombres de los empleados y los trabajos; por lo tanto, es esencial que use la función DISTINCTCOUNT para cualquier tipo de conteo.

Active Multi Job1_Measure

=

VAR x_Table = SUMMARIZECOLUMNS(

                                 EmployeeTable[EmployeeId],

                                 TREATAS({"Y"},EmployeeTable[IsActiveEmployee]),

                                 "DistinctCountOfJobs_Measure",DISTINCTCOUNT(EmployeeTable[Job])

                              )

RETURN

COUNTROWS(

           FILTER(

                   x_Table,

                   [DistinctCountOfJobs_Measure] > 1

                 )

          )

¿Puede validar el código anterior, si es infalible? Probé esta nueva medida en el archivo Power BI, es buena. Su idea de usar SUMMARIZECOLUMNS resultó muy útil; Utilicé la sintaxis de aquí.

[Syndicate_Admin]

Sí, la misma fórmula sigue funcionando.

[Syndicate_Admin]

@lbendlin

Tengo otro escenario, donde hay entradas duplicadas. Necesito tener DISTINCTCOUNT.

(Sin embargo, EmployeeId y EmployeeName están vinculados entre sí; un EmployeeId siempre hace referencia a un solo EmployeeName)

Consulte Power BI y Excel aquí.

Creo que soy bueno; Pero por si acaso, si tienes algo que comentar, por favor házmelo saber.

Fue realmente genial aprender una aplicación muy común de SUMMARIZECOLUMNS.