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

Hi again TSP

 

Please provide screen prints of the tables and columns next time.

You knew what you meant, but it was not 100% clear to me from your text.

 

I have added comments to this measure so hopefully you can fathom the solution now, but ask for more help if needed

 

My count =

 

-- get a list of all loans with blank assignments on TableA
VAR AssignmentsBlank =
CALCULATETABLE(VALUES(TableA[LoanID]),
FILTER(ALL(TableA),
TableA[AssignedDate] = BLANK() ))

 

-- get a list of all loans with application dates on TableB
VAR ApplicationsNotBlank =
CALCULATETABLE(VALUES(TableB[LoanID]),
FILTER(ALL(TableB),
TableB[ApplicationDate] <> BLANK() ))

 

-- get a list of all loans on both lists
VAT Onboth = INTESECT( ApplicationsNotBlank, AssignmentsBlank)

RETURN

 

-- counts loans on both
COUNTROWS(Onboth)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

"-- get a list of all loans on both lists
VAT Onboth = INTESECT( ApplicationsNotBlank, AssignmentsBlank)"

 

Just to make sure this is "VAR" on both and "INTERSECT"

 

I have done exactly that, the table actually loads with column names, but no data.

 

 

Thank you,

  • speedramps's avatar
    speedramps
    Super User
    6 years ago

    Sorry about the typo.  Yes of course INTESECT should be INTERSECT.

     

    I have created this example you. So you can look at the data, relationships and data measure
    Download this example 

     

    This counts the number of loans that have blank TableA[AssignedDate] and not blank TableB[ApplicationDate].

    Note only loans 2, 4 and 7 fullfill this condition. So My count = 3.

     

    My counts =


    -- get a list of all loans with blank assignments on TableA
    VAR AssignmentsBlank =
    CALCULATETABLE(VALUES(TableA[LoanID]),
    FILTER(ALL(TableA),
    TableA[AssignedDate] = BLANK() ))

    -- get a list of all loans with application dates on TableB
    VAR ApplicationsNotBlank =
    CALCULATETABLE(VALUES(TableB[LoanID]),
    FILTER(ALL(TableB),
    TableB[ApplicationDate] <> BLANK() ))

    -- get a list of all loans on both lists
    VAR Onboth = INTERSECT(ApplicationsNotBlank, AssignmentsBlank)

    RETURN
    -- counts loans on both
    COUNTROWS(Onboth)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I accept that this would probably work in most scenarios. However, I think there may be some limitations regarding our SQL queries.

     

    I can do the first part by itself and it will create a table, I can also do the same with the 2nd table.

     

    When I do both and try to intersect, nothing populates.