topic Re: Logical Count Function. in DAX Commands and Tips

Logical Count Function.

Anonymous — Thu, 28 May 2020 13:51:13 GMT

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 with SQL

Any tips on how to do this? I am new to PowerBi logic.

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 14:52:20 GMT

Hi TSP

Please consider this solution and leave kudos

COUNTBLANK(<columnname>)

this will count the number of rows with a blank value in the column.

Note it will return 0 if there are rows but no blanks,
but will retrun blank if the table has no rows at all.

Re: Logical Count Function.

Anonymous — Thu, 28 May 2020 14:55:45 GMT

Thanks for the suggestion, but I am wanting to count "B" Date if "A" date is blank.

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 15:10:19 GMT

Hi again TSP

Please consider this solution and leave kudos

Mycounter =
CALCULATE(COUNT('MyTable'[MyColumnB),
FILTER('MyTable'[MyColumnA)=BLANK())
)

The filter just selects the rows with ColumnA = blank

then the count counts the remaining rows with a populated ColumnB.

Any rows with a blank ColumnB will not be counted.

Re: Logical Count Function.

Anonymous — Thu, 28 May 2020 15:25:47 GMT

Would it matter if the blank date is in a different table? I am not getting any errors, but the count is not showing on a card.

Thank you,

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 15:43:56 GMT

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)

)

Re: Logical Count Function.

Anonymous — Thu, 28 May 2020 15:55:32 GMT

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.

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 16:54:08 GMT

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)

)

Re: Logical Count Function.

Anonymous — Thu, 28 May 2020 18:48:00 GMT

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,

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 21:18:49 GMT

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

Re: Logical Count Function.

Anonymous — Thu, 28 May 2020 21:34:44 GMT

See below: The original formula worked closely, but I had to add a filter to the graph to get it to work.

Now this is what I have, and the tables and counters are not populating.

To reiderate: When Assigned Date IS Blank, then count loans that have an application date.

Re: Logical Count Function.

speedramps — Thu, 28 May 2020 22:28:19 GMT

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)

Re: Logical Count Function.

Anonymous — Fri, 29 May 2020 13:54:40 GMT

"-- 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,

Re: Logical Count Function.

speedramps — Fri, 29 May 2020 15:40:58 GMT

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)

Re: Logical Count Function.

Anonymous — Wed, 03 Jun 2020 13:19:33 GMT

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.