how to combine multiple FIND requirements

[DLU]

Dear community, 

I have a dataset with a column that contains different types of transactions. I only need to calculate with values that contain WO,  TO, SO or IC. I tried to solve it with a if and contain function but that didn't work. I've now solved it by creating a calculated column for each type but I think there should be an easier way in DAX. This is my calculated column now: 

Workorders = FIND("WO";INVENTTRANSORIG[REFERENCEID];1;0)

And I would do the same for the TO, SO and IC 

After that I would create the next calculated column to come to an overall selection: 

Included transactions = SWITCH(TRUE();INVENTTRANSORIG[Workorders]=1;1;

INVENTTRANSORIG[Salesorders]=1;1;

INVENTTRANSORIG[Term orders]=1;INVENTTRANSORIG[IC orders]=1;

1;0)

And after that I would calculate my needed value with:

 Total number of transactions = Calculate(SUMX(INVENTTRANSORIG;INVENTTRANSORIG[QTY]);INVENTTRANSORIG[Included transactions]=1)

Does anyone have an idea how I can put all this into 1 DAX formula to prevent performance issues?

Many thanks in advance!

[v-jayw-msft]

Hi @DLU ,

Has the problem be solved?

Please consider to mark the reply as solution if it's helpful.

[DLU]

No unfortunately my problem wasn't solved. I've now solved it by adjusting my SQL query and already filtering that, so I can't accept any responses with the solution

[rsbin]

@DLU ,

Appears to me, you want to make use of the IN function in DAX. So something like this:

Number of Transactions = CALCULATE( SUM( Inventory[QTY]),
                              FILTER(Inventory, Inventory[ReferenceID] IN { "WO", "TO", "SO", "IC" } ))

Not sure if you need to use SUM or SUMX depending on your specific purpose.

Hope this gives you some ideas to simplify your DAX.

Regards,

[DLU]

Dear  rsbin, 

I've tried and I'm not getting an error but I'm not getting any data either. Just for your information. The referenceID is never only WO or SO etc. The WO is part of a bigger number like WO-001 or TXN-WO001. Could that now be the problem? 

Regards, Diana

[rsbin]

@DLU ,

Yes, that is the problem.  Based on your description, I assumed the Column value was simply the 2 character code.  I would suggest you create another Calculated Column isolating the codes you are searching for.  There are a number of DAX Text functions similar to those in Excel that can be used to extract these codes from the bigger number.

https://learn.microsoft.com/en-us/dax/text-functions-dax

Hope you are able to carry forward.  If not, please post sample data as a table (not an image) and I will try to find some time to assist.

Regards,