Forum Discussion
Dax formula combining different filters
HI
I Have the table below.
| CID | INSTANCE_NUMBER | TASK | TASK_STATUS | TASK_COMPLETED_DATE2 | TASKACTUALPERFORMER | CUID |
| 1000483 | 1224496 | Partner Acknowledgement - 1225829 - 1000543IT03 | Received | John Smith | 1000543ES01 | |
| 1000543 | 1229283 | Partner Acknowledgement - 1225829 - 1000543IT02 | Closed | John Smith | 1000543SG01 | |
| 1000543 | 1229282 | Partner Acknowledgement - 1225829 - 1000543IT01 | CLosed | John Smith | 1000543CH01 | |
| 1000543 | 1229279 | Partner Acknowledgement - 1225829 - 1000543IT00 | Received | John Smith | 1000543BE01 | |
| 1000543 | 1229281 | Partner Acknowledgement - 1225829 - 1000543IT01 | Received | Local Partner Team(ICP) | 1000543IT01 | |
| 11999 | 1254113 | Greenlight Checks - CR | Received | SL IST Team | ||
| 12060 | 1254160 | Greenlight Checks - CR | Received | SL IST Team | ||
| 10486 | 1254256 | Greenlight Checks - CR | Received | SL IST Team | ||
| 1000483 | 1224496 | Partner Acknowledgement - 1217776 - 1000483BE01 | Received | Local Partner Team(ICP) | 1000483BE01 | |
| 1000483 | 1224495 | Start Partner Engagement - 1225905 - 1000502BE01 | Received | Carolina Garcia | 1000483AT01 | |
| 1000502 | 1230579 | Start Partner Engagement - 1225905 - 1000502BE01 | Received | Carolina Garcia | 1000502BE01 | |
| 1000483 | 1224496 | Start Partner Engagement - 1217776 - 1000483BE01 | Closed | 28-JAN-22 | Maria Wells | 1000483BE01 |
| 1000362 | 1212423 | Start Partner Engagement - 1191496 - 1000362BE01 | Received | Maria Wells | 1000362BE01 | |
| 1000543 | 1229281 | Start Partner Engagement - 1225829 - 1000543IT01 | Closed | 31-JAN-22 | Robert Monte | 1000543IT01 |
| PIDA | 1244550 | Partner Acknowledgement - 1242428 - PIDAIT03 | Closed | 28-JAN-22 | Robert Monte | PIDAIT03 |
What I am trying to do is to create a Dax formula (or different way) to get a list of CUID that meet both requirements:
1st ---> CUID that contains the task "Start Partner Engagement" and the TaskActualPerformes is "Robert Monte" and "Maria Wells"
Once I have that list...i need to Cross the list of CUID with the ones that meet the 2nd requirement.
2nd ---> CUID that contains the Task "Partner Acknowledgment" and the status is closed.
What I currently do in Excel is create different pivot tables and then a VLOOKUP
Trying different formulas but with no success. Any tip?
thanks,
Hello romovaro.
If I'm understading the scenario correctly, I think you can create a table with the following DAX:Booked by Project Team = VAR MeetsFirstRequirement = CALCULATETABLE( VALUES( 'BMPS LOF Report'[CUID] ), CONTAINSSTRING( 'BMPS LOF Report'[TASK], "Start Partner Engagement" ) && 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Robert Monte" || 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Maria Wells" ) VAR MeetsBothRequirements = CALCULATETABLE( VALUES( 'BMPS LOF Report'[CUID] ), MeetsFirstRequirement, 'BMPS LOF Report'[TASK_STATUS] = "Closed", CONTAINSSTRING( 'BMPS LOF Report'[TASK], "Partner Acknowledgement" ) ) RETURN MeetsBothRequirementsOne issue that I had was that if I am understanding correctly, then none of the CUIDs in your sample table met the requirements, so the result was coming up blank. For testing, I changed the status from "Received" to "Closed" on Row 5 (which is the Partner Acknowledgement row for CUID 1000543IT01), so that it would meet both requirements,.
The results look like this:
I saved a .pbix here that you can download if it is helpful.
Here is the original data, with the value I changed highlighted:Hi romovaro. Thanks for the additional info. I think I'm understanding now.
Give this a try:Booked by Project Team = VAR MeetsFirstRequirement = CALCULATETABLE ( VALUES ( 'BMPS LOF Report'[CUID] ), CONTAINSSTRING ( 'BMPS LOF Report'[TASK], "Start Partner Engagement" ) && 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Robert Monte" || 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Maria Wells" ) VAR MeetsBothRequirements = CALCULATETABLE ( VALUES ( 'BMPS LOF Report'[CUID] ), MeetsFirstRequirement, 'BMPS LOF Report'[TASK_STATUS] = "Closed", CONTAINSSTRING ( 'BMPS LOF Report'[TASK], "Partner Acknowledgement" ) ) VAR Result = ADDCOLUMNS ( MeetsBothRequirements, "Date Closed", CALCULATE ( MAX ( 'BMPS LOF Report'[TASK_COMPLETED_DATE2] ), 'BMPS LOF Report'[TASK_STATUS] = "Closed", CONTAINSSTRING ( 'BMPS LOF Report'[TASK], "Partner Acknowledgement" ) ) ) RETURN Result
10 Replies
- SteveHailey
Solution Specialist
Hello romovaro.
If I'm understading the scenario correctly, I think you can create a table with the following DAX:Booked by Project Team = VAR MeetsFirstRequirement = CALCULATETABLE( VALUES( 'BMPS LOF Report'[CUID] ), CONTAINSSTRING( 'BMPS LOF Report'[TASK], "Start Partner Engagement" ) && 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Robert Monte" || 'BMPS LOF Report'[TASKACTUALPERFORMER] = "Maria Wells" ) VAR MeetsBothRequirements = CALCULATETABLE( VALUES( 'BMPS LOF Report'[CUID] ), MeetsFirstRequirement, 'BMPS LOF Report'[TASK_STATUS] = "Closed", CONTAINSSTRING( 'BMPS LOF Report'[TASK], "Partner Acknowledgement" ) ) RETURN MeetsBothRequirementsOne issue that I had was that if I am understanding correctly, then none of the CUIDs in your sample table met the requirements, so the result was coming up blank. For testing, I changed the status from "Received" to "Closed" on Row 5 (which is the Partner Acknowledgement row for CUID 1000543IT01), so that it would meet both requirements,.
The results look like this:
I saved a .pbix here that you can download if it is helpful.
Here is the original data, with the value I changed highlighted:- romovaro
Responsive Resident
thank you very much. it works 🙂
- romovaro
Responsive Resident
HI Steve
One "bonus question" in case you can help me. 🙂 I use the filter "month completed" using the month that "Partner Acknowledgement is closed"
from the table should be September 21
CLIENT_NAME CID INSTANCE_NUMBER TASK TASK_STATUS TASK_COMPLETED_DATE TASK COMPLETED DATE MONTH TASK COMPLETED Client A 380206 955194 Partner Acknowledgement - 913963 - 380206IE01 Closed 02-SEP-21 03.47.20.900000 PM 02-SEP-21 sep-21 Client A 380206 955194 Start Partner Engagement - 913963 - 380206IE01 Closed 20-AUG-21 10.19.03.001000 PM 20-AUG-21 aug-21 Client A 380206 955194 Partner Assignment - 913963 - 380206IE01 Closed 13-JUL-21 04.39.39.849000 PM 13-JUL-21 jul-21 but the table is using the first month of the column...JUL-21. ANy idea how to fix this? thanks
- SteveHailey
Solution Specialist
Sorry, I'm not quite understanding this second question. Could you give a little more info/screenshots? I'll be happy to try to help!
- romovaro
Responsive Resident
Hi Steve. Of course.
Once I have the list of CUID (thanks to your formula), management wants to see how many CUID were closed Monthly. The plan is to use the date (month) when the TASK= Partner Acknowledgement is TASK_STATUS = closed"
In the table you have different Month tAsk Completed
based on the Task. CUrrently it seems PowerBI is using the earliest date by default.
(Management thinks that CLient A was booked in July when it was booked 100% in September)
CLIENT_NAME CID INSTANCE_NUMBER TASK TASK_STATUS TASK_COMPLETED_DATE TASK COMPLETED DATE MONTH TASK COMPLETED Client A 380206 955194 Partner Acknowledgement - 913963 - 380206IE01 Closed 02-SEP-21 03.47.20.900000 PM 02-SEP-21 sep-21 Client A 380206 955194 Start Partner Engagement - 913963 - 380206IE01 Closed 20-AUG-21 10.19.03.001000 PM 20-AUG-21 aug-21 Client A 380206 955194 Partner Assignment - 913963 - 380206IE01 Closed 13-JUL-21 04.39.39.849000 PM 13-JUL-21 jul-21 Hope this helps