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

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