CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Rana Usman
Rana Usman

Reputation: 1051

Adding Category Filter in DAX of PowerBI

This may be a basic question but I am kind of stuck at it.

I have series of dax written to find an employee turnover.

It works on following formula.

Employees left / (Employee at start of year + Emp at end of year)/2 * 100.

It just works fine except that in the dax that calculates "Employees Left", it looks upon all the leavers while against every leaver, there's a column called "STATUS" that checks why the Employee left. It could be termination, or resignation, or Retirement.

I want to add a filter to the dax which finds Employees left only against "Resignations"

Employees who left =

VAR selectedDate = LASTDATE('Date'[Date])
VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))

RETURN
SUMX(NEW_Turnover_ADS,
IF(NEW_Turnover_ADS[LAST_WORKING_DATE]>=selectedDate12MonthsBefore && NEW_Turnover_ADS[LAST_WORKING_DATE]<=selectedDate, 1, 0)
)

I am currently using this dax.

I tried something as follow but it doesnt works.

Employees who left filter =

VAR selectedDate = LASTDATE('Date'[Date])
VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))

RETURN
SUMX(FILTER(NEW_Turnover_ADS, NEW_Turnover_ADS[HR_STATUS] = 'RESIGNATION', NEW_Turnover_ADS,
IF(NEW_Turnover_ADS[LAST_WORKING_DATE]>=selectedDate12MonthsBefore && NEW_Turnover_ADS[LAST_WORKING_DATE]<=selectedDate, 1, 0)
) )))))

Any help in this regard would be appreciated.

Please note I only need to add filter by category in above dax - rest is working fine.

Upvotes: 0

Views: 55

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Your attempt seems reasonable except for syntax errors.

Try this:

Employees who left =
VAR selectedDate = LASTDATE ( 'Date'[Date] )
VAR selectedDate12MonthsBefore = NEXTDAY ( SAMEPERIODLASTYEAR ( selectedDate ) )
RETURN
    SUMX (
        FILTER (
            NEW_Turnover_ADS,
            NEW_Turnover_ADS[HR_STATUS] = "RESIGNATION"
        ),
        IF (
            NEW_Turnover_ADS[LAST_WORKING_DATE] >= selectedDate12MonthsBefore
                && NEW_Turnover_ADS[LAST_WORKING_DATE] <= selectedDate,
            1,
            0
        )
    )

Upvotes: 1

Related Questions