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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
romovaro
Post Partisan
Post Partisan

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
Post Partisan
Post Partisan

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

 

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.