Increase Count on Status & Product Change

[SethuPower]

Hello All,

Product and Status is the columns i have and need data in column Group as output.

Group number to change when the status changes and Product changes

Could you please help with the DAX query

@Ashish_Mathur - I could see you have helped on similar query. Could you please assist on this one

Product	Status	Group
Soap	Approved	1
Soap	Approved	1
Soap	Removed	2
Soap	Removed	2
Soap	Sales	3
Soap	Removed	4
Soap	Others	5
Soap	Removed	6
Soap	Removed	6
Soap	Removed	6
Soap	Removed	6
Soap	Removed	6
Soap	Others	7
Soap	Others	7
Soap	Others	7
Box	Approved	1
Box	Removed	2
Box	Removed	2
Box	Others	3
Box	Sales	4
Box	Sales	4

[Ashish_Mathur]

Hi,

Could you share the link of the post where you saw my solution to a similar problem.

[SethuPower]

hi Ashish,

Thanks for your reply. Unfortuantely couldn't find that post now. Any luck based on the data example?

[Zubair_Muhammad]

@SethuPower 

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5PLFDSUXIsKCjKL0tNUYrVwSsWlJpLhFBwYk5qMQE1/iUZqUWEFFFTCNNCvCJO+RXoYQARQjYalwiGOYgQQeHHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"ALL", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(
Table.Group([ALL], {"Status","Product"}, {{"ALL", each _, type table }},GroupKind.Local),"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Status", "ALL", "Index"}, {"Status", "ALL", "Index"}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Expanded Custom", "ALL", {"Status"}, {"Status.1"})
in
    #"Expanded ALL"

[Zubair_Muhammad · Accepted solution]

@SethuPower 

Please see the attached file for steps as well