Need Help with this DAX calculation

[kkoc3karan]

Data

CRM ID	CRM Close Date	Task ID	Task Close Date
1	12/07/2023	1.1	14/07/2023
1	12/07/2023	1.2	15/07/2023
1	12/07/2023	1.3	16/07/2023
2		2.1	17/07/2023
2		2.2	18/07/2023
2		2.3
3		3.1	18/07/2023
3		3.2	19/07/2023

Need to Calcuate # Open CRM with no Open tasks 

In above eg CRM id 3 is the result as its open and all tasks have close date therefore my Measure should return 1 

My currect calc is as follows 

# OpenCRM with no open tasks =
Calculate(
DISTINCTCOUNT(Fct_Crm[CRM_ID]),
not(isblank(Fct_Crm[Task Close Date]),
ISBLANK(Fct_Crm[CRM_det_dte])
))

Doest give me correct answer

Any help will be appreciated 

[kkoc3karan · Accepted solution]

I have figured out however i am unsure if this is the most optimal way 

happy for someone to suggest better way 

countx(
    filter(
        summarize(
                test,
                test[CRM ID],
                test[CRM Close Date],
                "_1",
                countrows(
                            filter(
                                    test,
                                    not isblank(test[Task Close Date])
                            )
                ),
                "_2",
                countrows(test)
        ),
        isblank(test[CRM Close Date]) && [_1]=[_2]
    ),
    test[CRM ID]
)

[Ritaf1983]

Hi @kkoc3karan 
if I understand you correctly please update your dax to :

counter = CALCULATE(

    DISTINCTCOUNT('Table'[Task ID]),

    NOT(ISBLANK('Table'[CRM Close Date])),

    NOT(ISBLANK('Table'[Close Date]))

)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[kkoc3karan]

Hi @Ritaf1983 

Thanks for responding this doesnt give me the correct result .

If i add another record to the dataset it still gives me 1 . It should give me 2 as there are 2 CRMs open(close date null) where all tasks are closed ( close date populated) 

CRM IDCRM Close DateTask IdTask Close Date

1	Wednesday, 12 July 2023	1.1	Friday, 14 July 2023
1	Wednesday, 12 July 2023	1.2	Saturday, 15 July 2023
1	Wednesday, 12 July 2023	1.3	Sunday, 16 July 2023
2		2.1	Monday, 17 July 2023
2		2.2	Tuesday, 18 July 2023
2		2.3
3		3.1	Tuesday, 18 July 2023
3		3.2	Wednesday, 19 July 2023
4		4.1	Monday, 17 July 2023
4		4.2	Tuesday, 18 July 2023