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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.