Measure that counts, based on, if cell contains a word or multiple words

[Bassehave]

Hey guys and gals,

I have a query that contains two columns, [Sales Order Number] (..which is unique) and [Services].

For each Sales Order Number there is a combination of words in Services

111	Parcel
112	Parcel, Parcel
113	Parcel, Parcel, Parcel
114	Parcel, Parcel, Parcel, Truck
115	Parcel, Parcel, Truck
116	Parcel, Truck
117	Truck
118	Truck, Truck
119	Truck, Truck,Truck
121	Truck, Truck, Parcel

I need a way to count orders where:

Min. 1 Truck + 1 Parcel = Parcel Split

Min. 2 Truck = Truck Split

Min. 2 Truck + 1 Parcel = Both Parcel Split and Truck Split

And I need a measure that counts all "Truck" and a measure that counts all "Parcel"

Explaination:

I'm trying to do a calculation on the split percentage of orders. So I need to know how many orders where considered a Parcel Split and how many a Truck Split and then divide it by the total amount of orders.

I hope it makes sense and thanks in advance, for the support,

Bassehave

[puneetvijwani · Accepted solution]

@Bassehave  Based on my understanding of the problem , I suggest we should create first these two measures to count the Number of times Parcel occurs in a text column and number of times Truck occurs , below can be used

Parcel Count =

Var countwholesentencelength =

If( HASONEVALUE(Services[Services]) ,

LEN(VALUES(Services[Services])),0)

Var countwholesentencelengthwithoutParcel=

  If( HASONEVALUE(Services[Services]) ,

LEN(SUBSTITUTE(VALUES(Services[Services]),"Parcel","")),0)

return

DIVIDE(countwholesentencelength-countwholesentencelengthwithoutParcel ,6,0)

Truck Count =

Var countwholesentencelength =

If( HASONEVALUE(Services[Services]) ,

LEN(VALUES(Services[Services])),0)

Var countwholesentencelengthwithoutTruck=

  If( HASONEVALUE(Services[Services]) ,

LEN(SUBSTITUTE(VALUES(Services[Services]),"Truck","")),0)

return --countwholesentencelength

DIVIDE(countwholesentencelength-countwholesentencelengthwithoutTruck ,5,0)

Now Lets try cover the Count of Parcel Split , based on this logic you can cover the rest of others

Count of Parcel Split =

IF (

    ISINSCOPE ( Services[Services]),

    IF (

        [Parcel Count]>=1 && [Truck Count] >=1 ,

        1,

        BLANK ()

    ),

    SUMX (

        VALUES ( Services[Services] ),

        IF (

        [Parcel Count]>=1 && [Truck Count] >=1 ,

        1,

        BLANK ()

    )

    )

)

Note that table i  tried this on looks like below ( for some reason first row of your shared data was skipped in loading i.e 111 id but ignore that as thats not relevant )
Results also shared in the snapshot

-------------------------------------------------------------------------------------------------------------------------------

Good day, amigo! Have I provided the solution? If so, please let me know by adding the lovely, sweet "solution" tag to my post. And hey, if you're feeling very kind, give me a Kudos; after all, who doesn't enjoy a little digital gratitude?