Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks for the suggestion, but I am wanting to count "B" Date if "A" date is blank.
Hi again TSP
Please consider this solution and leave kudos
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,
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)
)
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.
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,
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)
)
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.
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)
"-- 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 =
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |