Countrows help

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