Home › Forums › Power Pivot › Determine if blank and count
This topic contains 2 replies, has 2 voices, and was last updated by KCantor 8 years, 1 month ago.
-
AuthorPosts
-
March 15, 2016 at 5:11 pm #3759
I am attempting to create an expression to count the number of rows if a field is blank/null. I am getting an error that says the value cannot be determine: either the row does not exist or there is no current row for the column. This is the expression I am using:
Number of Single Package shipments:=CALCULATE(DISTINCTCOUNT([No_]),
[First Package No_]=””)
The field [No_] is the package id number. If the shipment contains multiple packages, the [First Package No_] field will have entries. If the shipment does not contain multiple packages, the [First Package No] will be blank. I need to know how many shipment id numbers are associated with a single package as shipments with multiple packages can have 1 or more packages and is not a reliable way to calculate the actual number of shipments sent out.
I know the error is not telling me the expression is wrong, just that it cannot be completed without a row usage. I am assuming that this is a situation where a calculated column would be better. Since I brought the data in through PowerQuery I assume that I need to add the column in PowerQuery but do not know the required ‘M’ for that. If it helps, the table is ‘BB Posted Package’.
As always, any help is greatly appreciated!
March 15, 2016 at 6:31 pm #3760Hi,
You do not need to create a calculated column!
I believe the error message is telling you, in this case, to precede the column name with the table name.
IF [No_] and [First Package No_] both are columns in ‘BB Posted Package’, try:
Number of Single Package shipments :=
CALCULATE (
DISTINCTCOUNT ( ‘BB Posted Package'[No_] ),
FILTER ( ‘BB Posted Package’, [First Package No_] = “” )
)You will probably need to replace the single and double quotation characters, I think they will be converted to other characters during save.
Tom
March 15, 2016 at 6:39 pm #3764You are absolutely correct! I am happy to know this was a simple fix. Thank you very much, Tom, for giving so much of your time here in the forum to help those of us still learning the nuances of DAX.
Kristi
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.