topic Re: Count on the basis of string in DAX Commands and Tips

Count on the basis of string

Amitkr174 — Wed, 17 May 2023 12:14:02 GMT

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

Re: Count on the basis of string

tamerj1 — Wed, 17 May 2023 13:29:41 GMT

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] )

Re: Count on the basis of string

Amitkr174 — Wed, 17 May 2023 13:47:19 GMT

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"))))

Re: Count on the basis of string

tamerj1 — Wed, 17 May 2023 14:56:29 GMT

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.

Re: Count on the basis of string

ThxAlot — Sat, 20 May 2023 15:28:34 GMT

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"

Re: Count on the basis of string

Amitkr174 — Tue, 23 May 2023 17:28:50 GMT

Thanks a lot, that is working fine.