DAX - measure filtered by another measure with SUMMARECOLUMNS - equivalent of T-SQL GROUP, HAVING,..

[snph1777]

I have the below scenario:

I have a table called EmployeeTable; it has 16 distinct employees, but each employee can have more than one row,
based on the number of jobs she has had ; say, George has 2 jobs (Painter, Driver), and hence there are two rows for him.

(EmployeeId and EmployeeName are tied to each other though; an EmployeeId always refers to one EmployeeName only)

The column: EmployeeTable[IsActiveEmployee] determines whether an employee is active ?

This column can have only "Yes" or "No" values, abbreviated by "Y" and "N" respectively.

Now, the main requirement:

Develop a measure to get the headcount of ACTIVE employees with multiple jobs.

1) First identify the employees who have the value "Y" for the column EmployeeTable[IsActiveEmployee].

2) Then, filter this list further, restricting it only to employees with more than one (distinct) job.

3) Then, get a DISTINCTCOUNT of the employees.

There are 16 distinct employees; 11 of them are active; among these 11 active folks, I need to get a headcount of folks with more than one job.

I need the answer as 5, i.e. five folks have more than one job, and still active.

How do I get this ?

Can someone provide some suggestion ?

I have detailed everything in this .pbix file and Excel file.

[snph1777]

Also, I want to mentioned that this question is a typical T-SQL language (Microsoft SQL Server database) business case; it can be protyped for many situations.

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--

I am posting this because other users who are making the transisiton from T-SQL to DAX can benefit; i.e. how to use SUMMARIZECOLUMNS in DAX to get a solution equivalent in T-SQL using GROUP BY, HAVING, and a set of SELECT statements...

Remember that there is only one table I need to work on - #EmployeeTable; if you have more than one table, you need to make some minor (though not a lot of) adjustments to the solution I posted.

[lbendlin]

You may be overcomplicating things a little.

[snph1777]

Thanks   @lbendlin

Does this also work when the IsActiveEmployee value changes across Jobs ? Say, I have James and Jack below, I want Jack to be included, but not James, even though both have multiple jobs; Jack has had two jobs while being active, while James has had only one job while being active.

Now, I need the answer to change to 4. Is your DAX code a fool-proof solution to such changes ?

(I know, I did not post this scenario earlier, because I wanted the question to be as simple as possible, to start with.)

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[snph1777]

See the Power BI file and Excel file; everything stated there.

[snph1777 · Accepted solution]

@lbendlin

Thanks for your idea; I believe I got this; only thing, you did not include the Job in your formula; in my actual project, I have duplicate of employee names and jobs; so it is essential I use DISTINCTCOUNT function for any type of count.

Active Multi Job1_Measure

=

VAR x_CT = SUMMARIZECOLUMNS(

                             EmployeeTable[EmployeeId],

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

                             "DistinctCountOfJobs_Measure",DISTINCTCOUNT(EmployeeTable[Job])

                            )

RETURN

COUNTROWS(

           FILTER(

                   x_CT,

                   [DistinctCountOfJobs_Measure] > 1

                 )

          )

Can you validate the above code, whether it is fool-proof ? I tried this new measure in the Power BI file, it is good. Your idea to use SUMMARIZECOLUMNS proved very useful; I used the syntax from here.

[lbendlin]

yes, same formula still works.

[snph1777]

@lbendlin

I have another scenario, where there are duplicate entires. I need to have DISTINCTCOUNT.

(EmployeeId and EmployeeName are tied to each other though; an EmployeeId always refers to one EmployeeName only)

See the Power BI and Excel here.

I think I am good; but just in case, if you anything to comment, please let me know.

It was really great to learn a very common applucation of SUMMARIZECOLUMNS.