Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Logical Count Function.

I am trying to calclulate the following: When a certain date is blank, count a related date.   IF(IsBlank(A Date)          ,COUNT(B Date)                      ,Blank()   *This is a direct query...
CALCULATE
DAX
DAX Calculation
Measures
speedramps's avatar
speedramps
Super User
6 years ago

Please can you elborate and describe the 2 files.

 

Are you saying you have 2 files each with a list of dates and you want to count which dates are on table A but not on table B?

 

This will count dates on B but not on A

 

My count =

VAR lista = VALUES(tableA[date])
VAR listb = VALUES(tableB[date])
RETURN
COUNTROWS(

EXCEPT(listb, lista)

)

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

See Below: All of my tables are Related Direct Queires from SQL. I am wanting to track how many loans are in the application process.

 

To do so, I am using dates. So in my main database, I am wanting to know how many loans that have an application date, but do not have an assigned date.

 

 

  • speedramps's avatar
    speedramps
    Super User
    6 years ago
    Please conisder a solution like this which creates a list of assigned loans and compares it to a list of applications =

    My count  =
    VAR AssignedLoans =
    CALCULATETABLE(VALUES(LoanTable[LoanID]),
    FILTER(ALL(LoanTable),
    LoanTable[AssignedDate] <> BLANK() ))

    VAR Applications =
    VALUES(ApplicationsTable[LoanID])

    RETURN
    COUNTROWS(
    EXCEPT( Applications, AssignedLoans)
    )
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This works almost exactly how I need it to, but I think I need to specify that the  loan "has" to have an application date. Would I include that filter in the 2nd VAR?

       

      Thank  you for your help,

      • speedramps's avatar
        speedramps
        Super User
        6 years ago

        Hi again TPN

        Thank you for your kudos, I enjoyed helping you during lockdown

         

        Sorry, I just assumed all the applications would have an application date.

         

        You can change the 2nd var using CALCULATETABLE with FILTER to just get a list of applications with an application date, like this ...

         

         

        My count =

         

        VAR AssignedLoans =
        CALCULATETABLE(VALUES(LoanTable[LoanID]),
        FILTER(ALL(LoanTable),
        LoanTable[AssignedDate] <> BLANK() ))

         

        VAR Applications =
        CALCULATETABLE(VALUES(ApplicationsTable[LoanID]),
        FILTER(ALL(ApplicationsTable),
        ApplicationsTable[AppliedDate] <> BLANK() ))

        RETURN
        COUNTROWS(
        EXCEPT( Applications, AssignedLoans)
        )