Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
4 years ago
Solved

calculate formula

 

HI

 

I have a question regarding DAX formula for the table below.

CLIENT_NAMECIDINSTANCE_NUMBERTASKTASK_STATUSTASK_COMPLETED_DATECUID
Customer 110002831199226Partner Acknowledgement - 1179493 - 1000283NO01Received 1000283NO01
Customer 110002831199224Partner Acknowledgement - 1179493 - 1000283DK01Received 1000283DK01
Customer 110002831199226Start Partner Engagement - 1179493 - 1000283NO01Closed21-DEC-21 10.58.55.181000 AM1000283NO01
Customer 110002831199224Start Partner Engagement - 1179493 - 1000283DK01Closed21-DEC-21 10.58.18.535000 AM1000283DK01
Customer 110002831199225Partner Acknowledgement - 1179493 - 1000283NL01Received 1000283NL01
Customer 110002831179493Central Kick OffReceived  
Customer 110002831179493IPM HandOffClosed16-DEC-21 03.05.00.528000 PM 
Customer 110002831199225Start Partner Engagement - 1179493 - 1000283NL01Closed16-DEC-21 03.08.45.815000 PM1000283NL01
Customer 110002831199228Partner Assignment - 1179493 - 1000283GB01Received 1000283GB01
Customer 110002831199225Partner Assignment - 1179493 - 1000283NL01Closed15-DEC-21 01.39.14.851000 PM1000283NL01
Customer 110002831199227Partner Assignment - 1179493 - 1000283RU01Received 1000283RU01
Customer 110002831199224Partner Assignment - 1179493 - 1000283DK01Closed17-DEC-21 02.53.07.009000 PM1000283DK01
Customer 110002831199226Partner Assignment - 1179493 - 1000283NO01Closed20-DEC-21 12.14.43.514000 PM1000283NO01
Customer 110002831179493Resource AllocationClosed15-DEC-21 04.05.50.863000 PM 
Customer 110002831179493Track Welcome EmailClosed14-DEC-21 09.18.48.935000 AM 
Customer 110002831179493Pre Engagement ProcessClosed15-DEC-21 11.24.57.889000 AM 
Customer 110002831179493Client System SetupClosed14-DEC-21 01.32.30.414000 PM 
Customer 110002831179493Greenlight ChecksClosed30-NOV-21 05.08.57.840000 PM 

 

I want to create a formula that calculates using the CID column and task Greenlights Checks and Closed

 

and returns a list of "distinccount" cuids from that CUID.

 

In the example above for customer CID = 1000283

The list of CUID should be;

1000283GB01
1000283NL01
1000283RU01
1000283DK01
1000283NO01

 

I was thinking:

 

GreenLights Checks =
var tab =
CALCULATETABLE (
VALUES ( 'BMPS LOF Report'[CID] ),
FILTER (
'BMPS LOF Report',
'BMPS LOF Report'[TASK] = "Greenlight Checks"
&& 'BMPS LOF Report'[TASK_STATUS] = "Closed"
)
)
return
CALCULATE (
...and then I will need the CUID part
 
Thanks and Happy weekend
 
 

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    romovaro I think I have a video on a similar task, check it here https://youtu.be/zS1IDl3DLak

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

    • romovaro's avatar
      romovaro
      Responsive Resident
      4 years ago

      HI Parry

       

      Video looks good. Facing some problems but I will check again. In the meantime i updated the formula to get the Total Counts of CUIDs:

       

      reenLights-Recount CUIDS =
      var tab =
      CALCULATETABLE (
      VALUES ( 'BMPS LOF Report'[CID] ),
      FILTER (
      'BMPS LOF Report',
      'BMPS LOF Report'[TASK] = "Greenlight Checks"
      && 'BMPS LOF Report'[TASK_STATUS] = "Closed"
      )
      )
      return
      CALCULATE (
      DISTINCTCOUNT( 'BMPS LOF Report'[CUID] ),
      INTERSECT ( VALUES('BMPS LOF Report'[CID] ), tab )
      )
       
      But the formula counts the "Blank Space" as a distinccount...and it's wrong. Null should not be in the total count.
      CUID
      380305CA01
      BLANK
      380305AE01
      380305SA01
      380305BH01
      380305TW01
      380305JP01
      380305KR01
      380305MY01
      380305MX01
      380305SG01
      380305SG02

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    romovaro try this:

     

    eenLights-Recount CUIDS =
var tab =
CALCULATETABLE (
VALUES ( 'BMPS LOF Report'[CID] ),
'BMPS LOF Report'[TASK] = "Greenlight Checks",
'BMPS LOF Report'[TASK_STATUS] = "Closed"
)
return
CALCULATE (
DISTINCTCOUNT( 'BMPS LOF Report'[CUID] ),
INTERSECT ( VALUES('BMPS LOF Report'[CID] ), tab ),
NOT ISBLANK ( 'BMPS LOF Report'[CUID] )
)

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.