DAX new measure - grouping users according to how many items they bought

[Anonymous]

Hi,

In the data set I have (please refer to the snip below) there is a column 'user_id' and 'qty', which I want the grouping to be based on.

'User_id', as the name implies, refers a user_id, but it is not unique in this data set, as the user may have made several orders/transactions and thus, may appear several times in the data set. 

'Qty' refers to the amount of certain products that were bought during a particular transaction.

I would like to group the users (create a new measure), who have bought >=10 products and who have bougth <10 products. 

Many thanks in advance!

[jbwtp]

Hi @Anonymous,

in DAX something like this:

GroupId_ = 
var uid = MAXx('myTable', 'myTable'[user_id])
return if (Sumx(Filter(All('myTable'),'myTable'[user_id] = uid), [qty])>10, "VIP", "Casual")

Or if you want to do it in PQ then somethig like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi1OLYrPTFHSUSosqQSSLoklqUqxOtFKjo6OQK4hCOsbGOobGRgZoYkb4RA3xiFugkPcFIe4GQ5xcxziFjjELXGIGxrgkkD1sZOTE0zCCJeEMS4JE1wSprgkkLwdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"user_id", type text}, {"qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"user_id"}, {{"Data", each _, Int64.Type}, {"GroupId", each if List.Sum([qty])>10 then "VIP" else "Casual", type text}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"Data", "GroupId"}),
    Output = Table.ExpandTableColumn(#"Removed Other Columns1", "Data",Table.ColumnNames(#"Changed Type")),
    #"Changed Type1" = Table.TransformColumnTypes(Output,{{"user_id", type text}, {"qty", Int64.Type}, {"Date", type date}})
in
    #"Changed Type1"

Kind regards,

John

[Anonymous]

Hi @jbwtp ,

as the 'user id' column is in text format, functions like sumx and maxx do not work. As a result unfortunately your solution does not work as well. Perhaps any other ideas?

I cannot change the 'user id' column to be in a number format as it contains multiple letters as well.

Thanks,

[jbwtp]

Hi @Anonymous,

Have you tested it? MAXX works seemlessly with text and dates, not just numbers. With anything that can be sorted pretty much. Can you test it on a clear/simple set of data? Maybe the problem somewhere else?

Cheers,

John

[Anonymous]

Hi John, 

This is what I get:

Thanks,

Martynas

[jbwtp]

Hi @Anonymous,

Do you mind having another look into the DAX formula in your post? I think there may be a couple of errors there. It is missing var VarName = (compare to the example in my post) in the first line, if you do what I think you do. Hence the return doesnot like it. In the return line you need to compare [User_Id] toVarName, not to itself.

Something like this:

User > 10 items = 
var uid = maxx('Full_list - final', [User_id])
return if( sumx( filter( all('Full_list - final'), 'Full_list - final'[User_id] = uid ), [qty] ) > 10, "more", "less" ) 

Kind regards,

John

[Anonymous]

Hi @jbwtp ,

Highly appreciate you sticking to help.

I have updated the DAX formula according to your post, however, the new column only gives back results with the value 'less', even though there are items in the qty field with >10 items.

Many thanks,

Martynas

[jbwtp]

Hi @Anonymous,

I think, you are adding this as a calculated column, not as measure, which is quite different scenario of use, because when PBI calculates columns the formula applies to each row as if it would be the only row in the table. Hence they all "less" as each individual row value is less than 10.

Can you create/use it as a measure? If not, I think it is better to do it in Power Query rather than DAX.

Kind regards,

John