Forum Discussion

Cameron_ITS's avatar
Cameron_ITS
New Member
2 years ago
Solved

Distinct Count from 2 Measures

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 fro...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

123abc's avatar
123abc
Community Champion
2 years ago

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.

Cameron_ITS's avatar
Cameron_ITS
New Member
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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