Forum Discussion
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...
"-- 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,
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)