Count on the basis of string

[Amitkr174]

Hi @amitchandak  @Mahesh0016  @JihwanKim  @Greg_Deckler @Ajendra  @ThxAlot 

I have a table as below. I need to count the orders and then provide the output as per below table. I want to count the orders and put it in the Occurence band, like if order is >3 and it was requeted by one 1 user, then output should be ">3 orders - 1"

I want a calculated column so that I can create a Graph. Please help on this. 

Row Labels	Count of Orders
Vikas Behel	5
TIKENDRA SINGH	2
Matthew Jackson	2
Martin Bonnor	2
Jake Mendoza	2
George Rose	2
Sarah Liao	2
John Gehrung	2
Joshua Zelman	2
Aaron Johnson	2
Clement Quek	1
Peter Hansen	1
Simeon Strang	1
Jeremy Willmott	1

Output
>3 Orders	1
2 Orders	9
1 order	4

[ThxAlot]

A typical, straightforward size-based cohort.

A generic PQ solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7dSsNAEAXgVxn2ujcKPkCrkjba0jaiYOjFYA/dJbszMLtB9OmNKaS5/eD8tK17Dx1nWsEjuoV7cKdF6942L8+7p+OSms2uWg98P/KWS/H4ppq/uqwycytBaKUiapPW3IG2kLP+8oQV1C6go2ZM1rCxp9fAesuqF6rgrZfLDLPvmT4RE9/Gl2wq9B+YX3qMSJBChx7dgHcj7lFgtGbJkAmbkDAUNMV43LpqDUP6oY8QY9JSrn76Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Labels" = _t, #"Count of Orders" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", type text}, {"Count of Orders", Int64.Type}}),

    #"Sorted List" = List.Sort(List.Distinct(#"Changed Type"[Count of Orders])),
    Threshold = #"Sorted List"{1}?,
    #"Joined Tables" = Table.NestedJoin(Table.FromList(List.RemoveLastN(#"Sorted List",1),Splitter.SplitByNothing(),{"Count of Orders"}), "Count of Orders", #"Changed Type", "Count of Orders", "Cnt", JoinKind.FullOuter),
    #"Transformed Columns" = Table.TransformColumns(#"Joined Tables", {{"Count of Orders", each _ ?? ">=" & Text.From(Threshold)}, {"Cnt", Table.RowCount}})
in
    #"Transformed Columns"

[Amitkr174]

@tamerj1  I am getting the below error. Please advise

I have created a Return Requestor colum. DAX is below:-

DAX:- 

IF(

COUNTX (

    FILTER (Table, EARLIER ( Table[Requestor_FullName] ) = Table[Requestor_FullName] ),

    Table[Requestor_FullName]

)>3,">3 Request",

IF(

COUNTX (

    FILTER (Table, EARLIER ( Table[Requestor_FullName] ) = Table[Requestor_FullName] ),

    Table[Requestor_FullName]

)=3,"3 Request",

IF(

COUNTX (

    FILTER (Table, EARLIER ( Table[Requestor_FullName] ) = Table[Requestor_FullName] ),

    Table[Requestor_FullName]

)=2,"2 Request", "1 Request"))))

[tamerj1 · Accepted solution]

@Amitkr174 

Segmants is a new disconnect table. You can insert manually or use my proposed DAX to create a new calculated table. 
Please refer to the sample PBIX file that is attached in my original reply for more details. 

[Amitkr174]

Thanks a lot, that is working fine.

[tamerj1]

Hi @Amitkr174 
Please refer to attached sample file wit the proposed soltion.

First you need to manually create the semants table

then use the following measure

# Customers = 
SUMX (
    GROUPBY (
        FILTER (
            ADDCOLUMNS ( 
                CROSSJOIN ( 
                    VALUES ( 'Table'[Customer] ),
                    Segmants
                ),
                "@NumOfOrders", [Count of Orders]
            ),
            [@NumOfOrders] > Segmants[Low Limit]
                && [@NumOfOrders] <= Segmants[High Limit]
        ),
        [@NumOfOrders],
        "@Count", SUMX ( CURRENTGROUP ( ), 1 )
    ),
    [@Count]
)