Using FILTER with LEFT and OR

DAX DAX Calculations
1

Hello

I have a dataset coming via direct query from NAV item entry tables and I need to filter out exactly the transaction lines I need to get the correct quantities from the production line. I need to include rows where the item number starts with RM (raw material) or IM (semi processed). A typical item no is RM00012.
I can get the right answer for just RM or just IM by using;

Consumption Quantity = -DIVIDE((
                            CALCULATE(SUM('nav Item Ledger Entry'[Quantity]), 
                            FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Entry Type] = 5), 
                            FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Unit of Measure Code] = "PCS"),
                            FILTER('nav Item Ledger Entry', LEFT('nav Item Ledger Entry'[Item No_],2) = "RM"))),
                                [UOM Qty if KG],0)

but is there a way of doing this in one query? I have tried..

Consumption Qty = -DIVIDE(
                            (CALCULATE(SUM('nav Item Ledger Entry'[Quantity]), 
                            FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Entry Type] = 5), 
                            FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Unit of Measure Code] = "PCS"),
                            FILTER('nav Item Ledger Entry', 
                            LEFT('nav Item Ledger Entry'[Item No_],2) = "RM" && 
                            LEFT('nav Item Ledger Entry'[Item No_], 2) = "IM"))),[UOM Qty if KG],0)

I have tried....

Consumption Qty = -DIVIDE(
                        (CALCULATE(SUM('nav Item Ledger Entry'[Quantity]), 
                        FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Entry Type] = 5), 
                        FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Unit of Measure Code] = "PCS"),
                        FILTER('nav Item Ledger Entry', 
                            OR(
                        LEFT('nav Item Ledger Entry'[Item No_],2) = "RM" ,
                        LEFT('nav Item Ledger Entry'[Item No_], 2) = "IM"))),[UOM Qty if KG],0)

But neither work, also it seems the multiple filters are causing performance issues so is there a generally better way of tackling this?

Many thanks

2

Hi @LizzieTompsett

Try Like this. Let me know if any issue occurs.

FILTER('nav Item Ledger Entry', 'nav Item Ledger Entry'[Entry Type] = 5 && 
'nav Item Ledger Entry'[Unit of Measure Code] = "PCS" && 
(LEFT('nav Item Ledger Entry'[Item No_],2) = "RM" || LEFT('nav Item Ledger Entry'[Item No_],2) = "IM"))

Thanks
Ankit J

3

Hi @LizzieTompsett,

Try something like:

Consumption Qty = 
-DIVIDE(
	CALCULATE(SUM(‘nav Item Ledger Entry’[Quantity]),
		FILTER(‘nav Item Ledger Entry’, 
			‘nav Item Ledger Entry’[Entry Type] = 5 &&
			‘nav Item Ledger Entry’[Unit of Measure Code] = "PCS" &&
			LEFT(‘nav Item Ledger Entry’[Item No_],2) IN { "RM", "IM" }
		)
	),
	[UOM Qty if KG],0
)

.
If you need further assistance, please provide a sample PBIX especially for optimizaton issues.
I hope this was helpful.

4

Hi @LizzieTompsett, we’ve noticed that no response has been received from you since the 23rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

5

Hi Ankit
I have only just started working on this project again and your LEFT script within FILTER has worked, thank you so much, Lizzie