Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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" ) )
Proud to be a Super User!
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" ) )
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.
Proud to be a Super User!
Works perfectly! thank you!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |