Forum Discussion

ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago

Formula Help- Distinct count related to multiple criteria

Hi everyone-  I am trying to solve for an issue and have worked at it for way too long, need to finally ask for help. I am working on turnover metrics which requires Average Active employees for a d...
Need Help
ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago

dax 

Links below. Easiest example would be using even just the emplyee count today, or the average this period. All company should be about 1025. But if I filtered it to Finance, I would expect 82, or 15 for HR. 

 

As mentioned before- The Measure for Terminations as part of my Turnover calcultaion works and interacts with filters for Department and the Period/Quarter just fine; 

Terms = CALCULATE(DISTINCTCOUNT(Employment[EmployeeNumber]), ISBLANK(Employment[TerminationDate]) = FALSE())

 

Employment- https://drive.google.com/file/d/1ehwNoLXvNRpUu2DJuaKxp1q_sX-0g6Mt/view?usp=sharing

DimDate- https://drive.google.com/file/d/16mYPQ8nbK7JLlp3cpPqwgmpqdIPsPVA5/view?usp=sharing

 

Apologies for using Google Drive. My organization has our One Drive as internally shareable only. I also had to format a column on my DimDate query once I loaded to make the join work for both dates to have the same fomat- 

JoinEmployment = FORMAT('Date Term'[Date], "MM-DD-YYYY")
dax's avatar
dax
Community Support
6 years ago

Hi ehmacc,

Did you mean you want to get interactive based on date range slicer? If so, you could try below measure

 

Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Termination Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )

 

 If this is not what you want , please correct me and inform em your expected output.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

  • ehmacc's avatar
    ehmacc
    Frequent Visitor
    6 years ago

    dax

    That equation only returns the count of employees with termination dates in that range, it isn't picking up employees without a termination date, which I need included.

     

    I also had to change the filter to include Hire date as the first piece- it should filter for employees who were Hired before the last day of the date range who either had a term date greater than the first day of the date range or no term date. But this has not fixed the count problem- it is still only counting if they have a termination date. 

    Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Hire Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )