Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Dax formula combining different filters

HI

 

I Have the table below.

 

CIDINSTANCE_NUMBERTASKTASK_STATUSTASK_COMPLETED_DATE2TASKACTUALPERFORMERCUID
10004831224496Partner Acknowledgement - 1225829 - 1000543IT03Received John Smith1000543ES01
10005431229283Partner Acknowledgement - 1225829 - 1000543IT02Closed John Smith1000543SG01
10005431229282Partner Acknowledgement - 1225829 - 1000543IT01CLosed John Smith1000543CH01
10005431229279Partner Acknowledgement - 1225829 - 1000543IT00Received John Smith1000543BE01
10005431229281Partner Acknowledgement - 1225829 - 1000543IT01Received Local Partner Team(ICP)1000543IT01
119991254113Greenlight Checks - CRReceived SL IST Team 
120601254160Greenlight Checks - CRReceived SL IST Team 
104861254256Greenlight Checks - CRReceived SL IST Team 
10004831224496Partner Acknowledgement - 1217776 - 1000483BE01Received Local Partner Team(ICP)1000483BE01
10004831224495Start Partner Engagement - 1225905 - 1000502BE01Received Carolina Garcia1000483AT01
10005021230579Start Partner Engagement - 1225905 - 1000502BE01Received Carolina Garcia1000502BE01
10004831224496Start Partner Engagement - 1217776 - 1000483BE01Closed28-JAN-22Maria Wells1000483BE01
10003621212423Start Partner Engagement - 1191496 - 1000362BE01Received Maria Wells1000362BE01
10005431229281Start Partner Engagement - 1225829 - 1000543IT01Closed31-JAN-22Robert Monte1000543IT01
PIDA1244550Partner Acknowledgement - 1242428 - PIDAIT03Closed28-JAN-22Robert MontePIDAIT03

 

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?

 

Booked by Project Team =
var tab =
CALCULATETABLE (
VALUES ( 'BMPS LOF Report'[CUID] ),
FILTER (
'BMPS LOF Report',
CONTAINSSTRING ('BMPS LOF Report'[TASK], " Start Partner Engagement ")
)
)
return
CALCULATE (
COUNT ('BMPS LOF Report'[CUID] ),
INTERSECT ( VALUES('BMPS LOF Report'[CUID] ), tab ),
FILTER (
'BMPS LOF Report',
'BMPS LOF Report'[TASK_STATUS] = "Closed"
&& CONTAINSSTRING ( 'BMPS LOF Report'[TASK], "Partner Acknowledgement" )
)
)

 

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 MeetsBothRequirements

     

    One 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's avatar
    SteveHailey
    Icon for Solution Specialist rankSolution 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 MeetsBothRequirements

     

    One 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's avatar
      romovaro
      Icon for Responsive Resident rankResponsive Resident

      thank you very much. it works 🙂

  • romovaro's avatar
    romovaro
    Icon for Responsive Resident rankResponsive 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_NAMECIDINSTANCE_NUMBERTASKTASK_STATUSTASK_COMPLETED_DATETASK COMPLETED DATEMONTH TASK COMPLETED
    Client A380206955194Partner Acknowledgement - 913963 - 380206IE01Closed02-SEP-21 03.47.20.900000 PM02-SEP-21sep-21
    Client A380206955194Start Partner Engagement - 913963 - 380206IE01Closed20-AUG-21 10.19.03.001000 PM20-AUG-21aug-21
    Client A380206955194Partner Assignment - 913963 - 380206IE01Closed13-JUL-21 04.39.39.849000 PM13-JUL-21jul-21

     

    but the table is using the first month of the column...JUL-21. ANy idea how to fix this? thanks

    • SteveHailey's avatar
      SteveHailey
      Icon for Solution Specialist rankSolution 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's avatar
        romovaro
        Icon for Responsive Resident rankResponsive 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_NAMECIDINSTANCE_NUMBERTASKTASK_STATUSTASK_COMPLETED_DATETASK COMPLETED DATEMONTH TASK COMPLETED
        Client A380206955194Partner Acknowledgement - 913963 - 380206IE01Closed02-SEP-21 03.47.20.900000 PM02-SEP-21sep-21
        Client A380206955194Start Partner Engagement - 913963 - 380206IE01Closed20-AUG-21 10.19.03.001000 PM20-AUG-21aug-21
        Client A380206955194Partner Assignment - 913963 - 380206IE01Closed13-JUL-21 04.39.39.849000 PM13-JUL-21jul-21

         

        Hope this helps