topic Re: Filter issue with "<=" in DAX Commands and Tips

Filter issue with "<="

Allie — Thu, 29 Oct 2020 15:29:39 GMT

Hello,

I am trying to get a chart that will show me Headcount progress over time. I found a video with a nice solution to that, but for some reason it does not work exactly how I want.

This is the formula:

Headcount = Calculate(COUNTROWS('[HR Actions] Actions in EC'), FILTER(values('[HR Actions] Actions in EC'[Hire Date]), '[HR Actions] Actions in EC'[Hire Date] <= MAX( Dates[Date])), filter(values('[HR Actions] Actions in EC'[Term Date]), OR('[HR Actions] Actions in EC'[Term Date] >= MIN(Dates[Date]), isblank('[HR Actions] Actions in EC'[Term Date] ) ) ) )

My issue is that instead of counting everyone filtered with Hire Date before or on a given Date, it only filters and counts people hired precisely on the given date, meaning the sign "<=" seems not to be working properly...

Any ideas what might be the problem??

Re: Filter issue with "<="

themistoklis — Thu, 29 Oct 2020 15:38:28 GMT

Allie

Can you tell us more about your data model?

You use both Hire Date and Term Date in your formula

Re: Filter issue with "<="

Anonymous — Thu, 29 Oct 2020 16:12:43 GMT

Please, first learn about how to build correct models:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Then, try to learn about relationships in DAX:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

Then, try to learn about time-intelligence in DAX:

https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-time-intelligence/

Then you'll be well equipped with knowledge to face issues like the one above.

Re: Filter issue with "<="

Payeras_BI — Thu, 29 Oct 2020 16:14:33 GMT

Hi Allie,

Make sure the relationship between these two dates ([Hire Date] & [Term Date]) and the [Date] from your Calendar Table is inactive.

Regards,

J. Payeras

Re: Filter issue with "<="

Allie — Fri, 30 Oct 2020 07:24:28 GMT

Thank you 🙂

I was sure they were inactive, but went to check again and indeed relation to Hire Date was active for some reason...