DAX - Multiple Conditions

[AndrewKent]

Hi all,

I have a rather nifty DAX calculation that gives me a rolling 12 month position on my data, based on one criteria. How do I update this so that it includes a second criteria of tbl_data_diversity_recruitment[Stage] = "e. Hired";

Hired 12MR Male:=CALCULATE (
    COUNTA ( tbl_data_diversity_recruitment[Requisition ID] ),
    FILTER (
        ALLEXCEPT (
            tbl_data_diversity_recruitment,
            tbl_departments_matrix[WL5 Area],
            tbl_departments_matrix[Functional Area],
            tbl_departments_matrix[Business Area],
            tbl_departments_matrix[Type],
            tbl_data_diversity_recruitment[Overall Area],
            tbl_data_diversity_recruitment[Stage],
            tbl_data_diversity_recruitment[Work Level]
        ),
        (
            tbl_data_diversity_recruitment[Month ID]
                >= MAX ( tbl_date_matrix[Month ID] ) - 11
        )
            && ( tbl_data_diversity_recruitment[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
    ),
    FILTER (
        ALLEXCEPT (
            tbl_data_diversity_recruitment,
            tbl_departments_matrix[WL5 Area],
            tbl_departments_matrix[Functional Area],
            tbl_departments_matrix[Business Area],
            tbl_departments_matrix[Type],
            tbl_data_diversity_recruitment[Overall Area],
            tbl_data_diversity_recruitment[Stage],
            tbl_data_diversity_recruitment[Work Level]
        ),
        tbl_data_diversity_recruitment[Gender] = "Male"
    )
)

Cheers,

Andy

[KHorseman · Accepted solution]

The same way you already added a second condition to your first filter statement. &&

Hired 12MR Male:=CALCULATE (
    COUNTA ( tbl_data_diversity_recruitment[Requisition ID] ),
    FILTER (
        ALLEXCEPT (
            tbl_data_diversity_recruitment,
            tbl_departments_matrix[WL5 Area],
            tbl_departments_matrix[Functional Area],
            tbl_departments_matrix[Business Area],
            tbl_departments_matrix[Type],
            tbl_data_diversity_recruitment[Overall Area],
            tbl_data_diversity_recruitment[Stage],
            tbl_data_diversity_recruitment[Work Level]
        ),
        (
            tbl_data_diversity_recruitment[Month ID]
                >= MAX ( tbl_date_matrix[Month ID] ) - 11
        )
            && ( tbl_data_diversity_recruitment[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
    ),
    FILTER (
        ALLEXCEPT (
            tbl_data_diversity_recruitment,
            tbl_departments_matrix[WL5 Area],
            tbl_departments_matrix[Functional Area],
            tbl_departments_matrix[Business Area],
            tbl_departments_matrix[Type],
            tbl_data_diversity_recruitment[Overall Area],
            tbl_data_diversity_recruitment[Stage],
            tbl_data_diversity_recruitment[Work Level]
        ),
        tbl_data_diversity_recruitment[Gender] = "Male"
        &&  tbl_data_diversity_recruitment[Stage] = "e. Hired"
    )
)

[AndrewKent]

I thought so too but I get the "tbl_data_diversity_recruitment cannot be determined in the current contexT" error message. Any thoughts?

[KHorseman]

Oh I just noticed you have that [Stage] column referenced in the ALLEXCEPT statement. So you can't filter on it because you just told it not to filter on it.

[AndrewKent]

Works perfectly! thank you!