topic Re: Measure with Filter in DAX Commands and Tips

Measure with Filter

Xaraja — Fri, 29 Apr 2022 19:23:41 GMT

I feel like this should be obvious but I am not finding anything on Google that seems relevant so maybe I'm missing something. I am working with an existing dataset that was working but now isn't so I'm trying to troubleshoot. I have two tables:

Warehouse transactions, a list of all events in the warehouse such as qty adjustments due to cycle counts, picking, packing, shipping, etc.
Warehouse inventory, a list of current inventory including current costing for each product

I have a measure that looks like this:

Before Dollar Value = Transactions[item_qty] * Transactions[Products.Price Multiplier] * RELATED(Warehouse[Average])

The measure is calculating the on hand qty of the product, multiplied by a price multiplier (sometimes the pricing is multiplied by 100 or 1000 because supply chain is complicated) and then by the average cost stored in the warehouse table.

My problem is that I need to filter the table somehow to only run this calculation when the transaction type is System Cycle Count (Transaction Type is another column in the Transactions table). I feel like I've seen calculations like this before, but I can't find anything now. Am I thinking about this wrong?

I could duplicate the table in Power Query and filter it to System Cycle Count, but it's about 7 million rows so I hate to do that.

Re: Measure with Filter

tackytechtom — Fri, 29 Apr 2022 19:35:20 GMT

Hi Xaraja ,

Do you mean something like this? 🙂

Before Dollar Value = 
CALCULATE ( 
    Transactions[item_qty] * Transactions[Products.Price Multiplier] * RELATED(Warehouse[Average]),
Transaction[System Cycle] = "System Cycle Count"
)

Let me know if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Measure with Filter

Xaraja — Fri, 29 Apr 2022 19:41:18 GMT

I get an error with that that says "A single value for column 'item_qty' in table 'Transactions' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Re: Measure with Filter

tackytechtom — Fri, 29 Apr 2022 19:46:48 GMT

Hi Xaraja ,

Did you try creating a calculated column instead of a measure with the code I provided above? If so, try it with a measure 🙂

Otherwise feel free to share some screenshots or even better, some example data!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Measure with Filter

v-chenwuz-msft — Wed, 04 May 2022 02:06:41 GMT

Hi Xaraja ,

Maybe you can try this code, use max to get the value of current item:

Before Dollar Value = 
CALCULATE ( 
    max(Transactions[item_qty]) * max(Transactions[Products.Price Multiplier]) * max(Warehouse[Average]),
Transaction[System Cycle] = "System Cycle Count"
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.