Forum Discussion

AndrewKent's avatar
AndrewKent
Helper I
8 years ago
Solved

DAX - Multiple Conditions

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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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" ) )

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      AndrewKent
      Helper I

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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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.