Home Forums Power Pivot Determine if blank and count

Tagged: , ,

This topic contains 2 replies, has 2 voices, and was last updated by  KCantor 2 years, 5 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #3759

    KCantor
    Participant
    • Started: 12
    • Replies: 29
    • Total: 41

    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!

    #3760

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    Hi,

    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

    #3764

    KCantor
    Participant
    • Started: 12
    • Replies: 29
    • Total: 41

    You 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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.