Measure with Filter

Question

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.

tackytechtom · Answer

Hi&nbsp;Xaraja&nbsp;,&nbsp;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"
)&nbsp;Let me know if this helps!&nbsp;/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

xaraja · Answer

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."

tackytechtom · Answer

Hi&nbsp;Xaraja&nbsp;,&nbsp;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!&nbsp;/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

v-chenwuz-msft · Answer

Hi&nbsp;Xaraja&nbsp;,
&nbsp;
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"
)
&nbsp;
Best Regards
Community Support Team _ chenwu zhu
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Measure with Filter

4 Replies

Related content

Dynamic measure filtering

Filter by Rank measure

Using measure as a filter

Measure not affected by filter

DAX MEASURE TO FILTER