Countrows help

[tberr86]

Working on a measure that calculates a count of homes based on two conditions: homes still being built and finished but unsold, without duplicates. I'm sure there are a few approaches to this, here is what I came up with:

Total WIP = VAR ActiveWIP = COUNTROWS(CALCULATETABLE('SCHEDHOUSEDETAIL','SCHEDHOUSEDETAIL'[activitycode]="Q75",ISBLANK('SCHEDHOUSEDETAIL'[actualfinishdate])))

            VAR UnsoldCompletedHomes = COUNTROWS(CALCULATETABLE('SCHEDHOUSEDETAIL','SCHEDHOUSEDETAIL'[activitycode]="Q75",NOT(ISBLANK('SCHEDHOUSEDETAIL'[actualfinishdate])),ISBLANK(RELATED('HOUSE MASTER'[contract_date])),NOT(ISBLANK(RELATED('HOUSE MASTER'[Start Date])))))

            RETURN ActiveWIP+UnsoldCompletedHomes

Hopefully this gives a clear idea of what the function is trying to accomplish. There is a many to one relationship between schedhousedetail and housemaster. I know I'm wrong in the RELATED function, but not sure how to accomplish this.

I appreciate the guidance!

[OwenAuger · Accepted solution]

Hi @tberr86 

As you've rightly said, RELATED is not required here. RELATED requires a row context which does not exist in a measure (unless introduced within the measure by an iterator). In any case, filters applied to tables on the 1-side of a 1:many relationship automatically "propogate" from the 1-side to the many-side table.

I would also suggest CALCULATE ( COUNTROWS (...) ) rather than COUNTROWS ( CALCULATETABLE (...) )

Also, since the "Q75" filter is common to both components of the measure, you could use an outer CALCULATE with that filter applied, and place the remaining filters within nested CALCULATE calls.

Putting all this together, you could write something like this:

Total WIP =
CALCULATE (
    VAR ActiveWIP =
        CALCULATE (
            COUNTROWS ( 'SCHEDHOUSEDETAIL' ),
            ISBLANK ( 'SCHEDHOUSEDETAIL'[actualfinishdate] )
        )
    VAR UnsoldCompletedHomes =
        CALCULATE (
            COUNTROWS ( 'SCHEDHOUSEDETAIL' ),
            NOT ISBLANK ( 'SCHEDHOUSEDETAIL'[actualfinishdate] ),
            ISBLANK ( 'HOUSE MASTER'[contract_date] ),
            NOT ISBLANK ( 'HOUSE MASTER'[Start Date] )
        )
    RETURN
        ActiveWIP + UnsoldCompletedHomes,
    'SCHEDHOUSEDETAIL'[activitycode] = "Q75"
)

Alternatively, you could create two separate measures for ActiveWIP and UnsoldCompleteHomes, and sum them in the Total WIP. 

Does the above measure give the expected result?

Regards,

Owen

[tberr86]

@OwenAuger ,

Thank you! Not just for the solution but for the explaination, really helped clarify how the functions work.