Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndrewKent
Helper I
Helper I

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

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
KHorseman
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Works perfectly! thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.