Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
romovaro
Responsive Resident
Responsive Resident

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

romovaro_0-1643639440675.png

 

 

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,

 

2 ACCEPTED SOLUTIONS
SteveHailey
Solution Specialist
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 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:

SteveHailey_0-1643678032213.png


I saved a .pbix here that you can download if it is helpful.

Here is the original data, with the value I changed highlighted:

SteveHailey_1-1643678315748.png

 

View solution in original post

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

View solution in original post

10 REPLIES 10
romovaro
Responsive Resident
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"

 

romovaro_0-1643711222056.png

 

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

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!

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"

 

romovaro_0-1643719590523.png

 

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

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:

 

SteveHailey_0-1643725357787.png

And then select "Don't summarize".

SteveHailey_1-1643725409333.png

Does that help at all in this case?

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_NAMECIDINSTANCE_NUMBERTASKTASK_STATUSTASK_COMPLETED_DATETASK_COMPLETED_DATE2MONTH_COMPLETED_DATE3CUID
Client BPIDA1244550Partner Acknowledgement - 1242428 - PIDAIT03Closed28-JAN-22 11.24.36.523000 AM28-JAN-22Jan-22PIDAIT03
Client BPIDA1244550Start Partner Engagement - 1242428 - PIDAIT03Closed20-DEC-21 04.16.03.242000 PM20-DEC-21Dec-21PIDAIT03
Client X10004011212416Partner Acknowledgement - 1204056 - 1000401MX01Closed19-JAN-22 05.05.35.913000 PM19-JAN-22Jan-221000401MX01
Client X10004011212416Start Partner Engagement - 1204056 - 1000401MX01Closed11-OCT-21 10.52.51.454000 AM11-OCT-21oct-211000401MX01

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

Thank you SO MUCH! it works

romovaro
Responsive Resident
Responsive Resident

 

 

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
Solution Specialist
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 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:

SteveHailey_0-1643678032213.png


I saved a .pbix here that you can download if it is helpful.

Here is the original data, with the value I changed highlighted:

SteveHailey_1-1643678315748.png

 

thank you very much. it works 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.