Forum Discussion

kkoc3karan's avatar
kkoc3karan
Frequent Visitor
3 years ago
Solved

Need Help with this DAX calculation

Data

 

CRM ID

CRM Close DateTask IDTask Close Date
112/07/20231.114/07/2023
112/07/20231.215/07/2023
112/07/20231.316/07/2023
2 2.117/07/2023
2 2.218/07/2023
2 2.3 
3 3.118/07/2023
3 3.219/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 

 

Need Help
  • kkoc3karan's avatar
    kkoc3karan
    3 years ago

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

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    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's avatar
      kkoc3karan
      Frequent Visitor
      3 years ago

      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

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

       

       

       

  • kkoc3karan's avatar
    kkoc3karan
    Frequent Visitor
    3 years ago

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