Problem with Filter And Or and Blank

[Jensej]

Hello 

I have a problem with a measure that don't work as i want to. 

For example i get some employees that already quit and also some active employees doesnt show. 

Total Emp = 

Var _Date= MIN(bi_date[date])

Var _Pri = CALCULATE(COUNT('bi_Emp'[Emp_ID]),

FILTER ( ALLEXCEPT(bi_Emp, bi_Emp[Gender],bi_Emp[Z_Group],bi_Emp[DepSalary]),'bi_Emp'[Hiring_Date] <= _Date &&

('bi_Emp'[Exit_Date] = BLANK() || 'bi_Emp'[Exit_Date] > _Date)) ) 

RETURN IF ( _Date = BLANK(), "Please pick a date", _Pri)

Somehow my filter with the Dates doenst seem to work. 

Hiring date has to be after _Date and Exit date has to be empty or later then _Date 

[Fowmy]

@Jensej 

Can you share some sample data and the expected result?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Jensej , refer to this example how date join work for both hire and terminated

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Jensej]

@amitchandak Still get the same wrong values when i use the Measures from your Link. 

@Fowmy Hard for me to send some sample data or something without spending a lot of time. 

I don't know if have a problem with the modeling. I have one table for Residents (bi_bewohner) and one for Employees (bi_mitarbeiter) that i use in my report. I have a Date (bi_datum) table where i have inactive joins to the both Residents/Employees tables. 

I join the hiring_date and exit_date from both tables with the date on bi_datum table.

 

[harshnathani]

Hi @Jensej ,

Since you cannot share sample data.

Try troubleshooting.

See if this is returning any values.

Total Emp =
VAR _Date =
    MIN ( bi_date[date] )
VAR _Pri =
    CALCULATE (
        COUNT ( 'bi_Emp'[Emp_ID] ),
        FILTER (
            ALL ( bi_Emp ),
            'bi_Emp'[Hiring_Date] <= _Date
                && 'bi_Emp'[Exit_Date] > _Date
        )
    )
RETURN
    IF (
        _Date
            = BLANK (),
        "Please pick a date",
        _Pri
    )

Uncomment parts and check wat value _date returns, etc.

Regards,

Harsh Nathani