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.

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.

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 = blankthen the count counts the remaining rows with a populated ColumnB.Any rows with a blank ColumnB will not be counted.

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])RETURNCOUNTROWS(EXCEPT(listb, lista))

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])RETURNCOUNTROWS(EXCEPT( Applications, AssignedLoans) )

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 TableAVAR AssignmentsBlank =CALCULATETABLE(VALUES(TableA[LoanID]),FILTER(ALL(TableA),TableA[AssignedDate] = BLANK() )) -- get a list of all loans with application dates on TableBVAR ApplicationsNotBlank =CALCULATETABLE(VALUES(TableB[LoanID]),FILTER(ALL(TableB),TableB[ApplicationDate] <> BLANK() )) -- get a list of all loans on both listsVAT Onboth = INTESECT( ApplicationsNotBlank, AssignmentsBlank)RETURN -- counts loans on bothCOUNTROWS(Onboth)

Logical Count Function. | Microsoft Fabric Community

14 Replies

speedramps
Super User
6 years ago
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.
- Anonymous
  Not applicable
  6 years ago
  Thanks for the suggestion, but I am wanting to count "B" Date if "A" date is blank.
  - speedramps
    Super User
    6 years ago
    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.

Forum Discussion

Logical Count Function.

14 Replies

Related content

Logic problem?

Count function is doubling

FX Conversion Logic

Counting nulls

Count function not counting correctly