Dax formula combining different filters

[romovaro]

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?

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,

[SteveHailey · Accepted solution]

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]

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]

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]

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

[SteveHailey]

I'm still struggling to understand, to be honest.

One thing I will say is that with "Table" visuals (not Matrix visuals as I see in your screenshot), sometimes Power BI will default to "Earliest" summarization. The way to change it is to click the down arrow as seen in the screenshot below:

And then select "Don't summarize".

Does that help at all in this case?

[romovaro]

HI Steve. Thanks for the TIP. I'm affraid I need to use the matrix table. 

I need Power BI to use the Month_completed_date that is shown in every CUID row when TASK= Partner Acknowledgement is TASK_STATUS = closed"

For client B, Power BI should use Month completed Date Jan-22 instead of Dec-21

For client X, Power BI should use Month Completed Date Jan-22 instead of Oct-21

I need to match the CUID i get from your formula and go to the row where task containts the string "Partner Acknowledgement" and the task_status is closed

CLIENT_NAME	CID	INSTANCE_NUMBER	TASK	TASK_STATUS	TASK_COMPLETED_DATE	TASK_COMPLETED_DATE2	MONTH_COMPLETED_DATE3	CUID
Client B	PIDA	1244550	Partner Acknowledgement - 1242428 - PIDAIT03	Closed	28-JAN-22 11.24.36.523000 AM	28-JAN-22	Jan-22	PIDAIT03
Client B	PIDA	1244550	Start Partner Engagement - 1242428 - PIDAIT03	Closed	20-DEC-21 04.16.03.242000 PM	20-DEC-21	Dec-21	PIDAIT03
Client X	1000401	1212416	Partner Acknowledgement - 1204056 - 1000401MX01	Closed	19-JAN-22 05.05.35.913000 PM	19-JAN-22	Jan-22	1000401MX01
Client X	1000401	1212416	Start Partner Engagement - 1204056 - 1000401MX01	Closed	11-OCT-21 10.52.51.454000 AM	11-OCT-21	oct-21	1000401MX01

[SteveHailey · Accepted solution]

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

[romovaro]

Thank you SO MUCH! it works

[romovaro]

HI Steve

I have a another issue using this table and combining with another excel file. I don't know why I cannot get the USD amount... maybe you can have a look if you have time.

https://community.powerbi.com/t5/Desktop/Combine-2-data-source-excel-and-summarize-column/m-p/231117...

[SteveHailey · Accepted solution]

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]

thank you very much. it works 🙂