topic Distinct Count from 2 Measures in DAX Commands and Tips

Distinct Count from 2 Measures

Cameron_ITS — Wed, 20 Mar 2024 02:46:34 GMT

Hello,

I have 2 tables, each with a measure. Eventually, i will have 3 measures.

Main data i am trying to count is the # of Unique Employees who are "Active" THIS MONTH. I am pulling data from Dataverse.

Active employee = 1 job filled out, or 1 observation performed as observer.

I currently have 2 measures, with date filters set to the visual of is in this month.

Measure 1 = Active Employees as Observer This Month

Measure 2 = Active Employees w Job filled Out

Both data fields that are called contain Employee Names. These match across tables.

I am needing to find the distinct count of total employees that are active. If one employee exists in both measures, they are counted as 1.

Re: Distinct Count from 2 Measures

123abc — Wed, 20 Mar 2024 05:08:54 GMT

To achieve the distinct count of total employees that are active, considering that if an employee exists in both measures they should be counted only once, you can create a new measure in Power BI using DAX. You can use the UNION function to combine the lists of active employees from both measures and then count the distinct values. Here's how you can do it:

Total Active Employees =
VAR ActiveEmployees1 =
VALUES('Table1'[EmployeeName]) // Replace 'Table1' with the actual name of your table for Measure 1
VAR ActiveEmployees2 =
VALUES('Table2'[EmployeeName]) // Replace 'Table2' with the actual name of your table for Measure 2
VAR AllActiveEmployees =
UNION(ActiveEmployees1, ActiveEmployees2)
RETURN
COUNTROWS(AllActiveEmployees)

Replace 'Table1' and 'Table2' with the actual names of your tables where the measures are defined. This DAX measure will give you the distinct count of total employees that are active, considering both measures. If an employee exists in both measures, they will only be counted once.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Distinct Count from 2 Measures

Cameron_ITS — Wed, 20 Mar 2024 15:38:56 GMT

Thank you for the reply!

When i attempt to put the name of the measures inside the value() funciton, my measure names do NOT populate, and when i enter the direct name I receive an error.

Re: Distinct Count from 2 Measures

Anonymous — Fri, 22 Mar 2024 08:53:56 GMT

123abc , Thanks for your contribution on this thread, the provided method is good.

Hi Cameron_ITS..

123abc has offered valuable advice on your post. He mentioned the VALUES function, not the VALUE function. The VALUES function can only refer to a column name, and measure cannot be referenced by this function. Based on your description, it appears that you are attempting to obtain a unique value for the number of items involved in two measures. You can create the following measure to achieve this:

NewMeasure = VAR _tab1 = CALCULATETABLE ( VALUES ( 'Table'[employee] ), FILTER ( 'Table', 'Table'[type] = "Observer" && MONTH ( 'Table'[HireDate] ) = MONTH ( TODAY () ) ) ) VAR _tab2 = CALCULATETABLE ( VALUES ( 'Table'[employee] ), FILTER ( 'Table', 'Table'[Job] = "Filled Out" ) ) VAR _tab3 = DISTINCT ( UNION ( _tab1, _tab2 ) ) RETURN COUNTROWS ( _tab3 )

Best Regards