topic Re: need help on how to take all rows for an item into account. in DAX Commands and Tips

need help on how to take all rows for an item into account.

DustinD — Mon, 30 Oct 2023 13:18:56 GMT

i have made a mock up of what my table looks like. so my goal is to take remsales minus onhand for a item and return y or n if we can cover all orders. so for example below 432-10 is in the table 2 times. when test runs which is what im using to show what im looking for. i want it to take all orders for a part not just a row level. the first item in the chart can be coverred but at the bottom we see the item again and we do not have enough to cover both orders so it should return n for that item number.

Re: need help on how to take all rows for an item into account.

FreemanZ — Mon, 30 Oct 2023 13:45:58 GMT

hi DustinD ,

try to add a calculated column like:

Test2 = VAR _t = FILTER(data, data[Item]=EARLIER(data[Item])) VAR _r = SUMX( _t, data[Resales]) VAR _o = MAXX( _t, data[On-Hand]) RETURN IF(_o>=_r, "y", "n")

it worked like:

Re: need help on how to take all rows for an item into account.

DustinD — Mon, 30 Oct 2023 13:48:32 GMT

will evrey row having its own order number effect using this dax? FreemanZ

Re: need help on how to take all rows for an item into account.

FreemanZ — Mon, 30 Oct 2023 13:52:02 GMT

no, it won't. just try and let us know.

Re: need help on how to take all rows for an item into account.

DustinD — Mon, 30 Oct 2023 14:05:47 GMT

this worked exactly how i wanted. thanks