Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Xaraja
Helper II
Helper II

Measure with Filter

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. 
4 REPLIES 4
tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.