topic Re: Countrows help in DAX Commands and Tips

Countrows help

tberr86 — Wed, 25 Jan 2023 21:17:22 GMT

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!

Re: Countrows help

OwenAuger — Thu, 26 Jan 2023 02:48:32 GMT

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

Re: Countrows help

tberr86 — Thu, 26 Jan 2023 14:27:46 GMT

OwenAuger ,

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