Forum Discussion
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_d...
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" ) )
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"
)
)
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.
Works perfectly! thank you!