calculate formula

[romovaro]

HI

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

CLIENT_NAME	CID	INSTANCE_NUMBER	TASK	TASK_STATUS	TASK_COMPLETED_DATE	CUID
Customer 1	1000283	1199226	Partner Acknowledgement - 1179493 - 1000283NO01	Received		1000283NO01
Customer 1	1000283	1199224	Partner Acknowledgement - 1179493 - 1000283DK01	Received		1000283DK01
Customer 1	1000283	1199226	Start Partner Engagement - 1179493 - 1000283NO01	Closed	21-DEC-21 10.58.55.181000 AM	1000283NO01
Customer 1	1000283	1199224	Start Partner Engagement - 1179493 - 1000283DK01	Closed	21-DEC-21 10.58.18.535000 AM	1000283DK01
Customer 1	1000283	1199225	Partner Acknowledgement - 1179493 - 1000283NL01	Received		1000283NL01
Customer 1	1000283	1179493	Central Kick Off	Received
Customer 1	1000283	1179493	IPM HandOff	Closed	16-DEC-21 03.05.00.528000 PM
Customer 1	1000283	1199225	Start Partner Engagement - 1179493 - 1000283NL01	Closed	16-DEC-21 03.08.45.815000 PM	1000283NL01
Customer 1	1000283	1199228	Partner Assignment - 1179493 - 1000283GB01	Received		1000283GB01
Customer 1	1000283	1199225	Partner Assignment - 1179493 - 1000283NL01	Closed	15-DEC-21 01.39.14.851000 PM	1000283NL01
Customer 1	1000283	1199227	Partner Assignment - 1179493 - 1000283RU01	Received		1000283RU01
Customer 1	1000283	1199224	Partner Assignment - 1179493 - 1000283DK01	Closed	17-DEC-21 02.53.07.009000 PM	1000283DK01
Customer 1	1000283	1199226	Partner Assignment - 1179493 - 1000283NO01	Closed	20-DEC-21 12.14.43.514000 PM	1000283NO01
Customer 1	1000283	1179493	Resource Allocation	Closed	15-DEC-21 04.05.50.863000 PM
Customer 1	1000283	1179493	Track Welcome Email	Closed	14-DEC-21 09.18.48.935000 AM
Customer 1	1000283	1179493	Pre Engagement Process	Closed	15-DEC-21 11.24.57.889000 AM
Customer 1	1000283	1179493	Client System Setup	Closed	14-DEC-21 01.32.30.414000 PM
Customer 1	1000283	1179493	Greenlight Checks	Closed	30-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

[parry2k · Accepted solution]

@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.

[parry2k]

@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]

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