How to create an index using dax comparing numbers on two columns

[Danielecc]

Hi everyone,

I have a problem to generate the calculated column "INDICE4" I want generate the "indece4" but only change if column "index" is distinct to the previous value. This is the example:

The data is:

City	Area	Organization	Item	From	To	Operator	Indice	Index	Indice2
N.Y.	NY_B2	Company_T1	Quantity	3	7	AND	10	1	1
N.Y.	NY_B2	Company_T1	Product	PRD03	PRD06	OR	11	2	2
N.Y.	NY_B2	Company_T1	Product	PRD08	PRD013	OR	12	2	3
N.Y.	NY_B2	Company_T1	Product	PRD15	PRD18	OR	13	2	4
N.Y.	NY_B2	Company_B5	Quantity	1	3	AND	14	1	5
N.Y.	NY_B2	Company_B5	Product	PRD04	PRD06	OR	15	2	6
N.Y.	NY_B2	Company_B5	Product	PRD09	PRD11	OR	16	2	7
N.Y.	NY_D4	Company_F5	Quantity	2	4	AND	22	1	1
N.Y.	NY_D4	Company_F5	Product	PRD02	PRD05	OR	23	2	2
N.Y.	NY_D4	Company_F5	Product	PRD07	PRD09	OR	24	2	3
N.Y.	NY_D4	Company_B2	Quantity	1	3	AND	25	1	4

Thank's a lot for help me, best regards...

[AlexisOlson · Accepted solution]

In general, it's easier to create index columns in the query editor rather than in DAX, so I'm providing a solution in M instead. It's possible in DAX but I'm passing on writing RANKX stuff for the moment. Maybe another user will jump in.

To get this index, I would group on the first four columns to set the index granularity, then group on City and Area, generating an index for each of these, then expand and merge back with the original table.

Here's the full M query you can paste into your Advanced Editor to walk through the applied steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFBD0NAEIX/i7OInd1Fj1XpUVvpRURE2otDaRoO/n1nGWIJ4jD71ka+zHsvSYzQii3DNMI48wH1Un2+edlmT4YfjyYv66Ju8cpxXJxzGODJbHV0k5rbjPuvejevWt2iwOakDuotUgD1D3RzjOSRMj6igFD8GIpJUm8kcSKJLZIv9YgYxUQRCXqSuwzdmFhEJGkb5yDpRL7YSHKI5M5IgZiQrjNffRCDL4CV6pcMfRsglcM2MKQ8r36P5E789SSx0rxG6uJa7QskPWHn6R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Area = _t, Organization = _t, Item = _t, From = _t, To = _t, Operator = _t, Indice = _t, Index = _t, Indice2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Area", type text}, {"Organization", type text}, {"Item", type text}, {"From", type text}, {"To", type text}, {"Operator", type text}, {"Indice", Int64.Type}, {"Index", Int64.Type}, {"Indice2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City", "Area", "Organization", "Item"},{}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"City", "Area"}, {{"AllRows", each  Table.AddIndexColumn(_, "Index4", 1, 1, Int64.Type), type table [City=nullable text, Area=nullable text, Organization=nullable text, Item=nullable text, Index4=Int64.Type]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"Organization", "Item", "Index4"}, {"Organization", "Item", "Index4"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"City", "Area", "Organization", "Item"}, #"Expanded AllRows", {"City", "Area", "Organization", "Item"}, "Expanded AllRows", JoinKind.LeftOuter),
    #"Expanded Expanded AllRows" = Table.ExpandTableColumn(#"Merged Queries", "Expanded AllRows", {"Index4"}, {"Index4"})
in
    #"Expanded Expanded AllRows"

[Danielecc]

@AlexisOlson  I try with your M code, and was very difficult because I am new on Power BI, DAX and M.

but, your explanation was very useful.

I changed your M code to this:

let
Origen = Excel.Workbook(File.Contents("C:\Users\danie\Desktop\Criterio Matriz.xlsx"), null, true),
Criteria_Table = Origen{[Item="Criteria",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Area", type text}, {"Organization", type text}, {"Item", type text}, {"From", type text}, {"To", type text}, {"Operator", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"City", "Area", "Organization", "Item"},{}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"City", "Area"}, {{"AllRows", each Table.AddIndexColumn(_, "Index4", 1, 1, Int64.Type), type table [City=nullable text, Area=nullable text, Organization=nullable text, Item=nullable text, Index4=Int64.Type]}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"Organization", "Item", "Index4"}, {"Organization", "Item", "Index4"}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"City", "Area", "Organization", "Item"}, #"Expanded AllRows", {"City", "Area", "Organization", "Item"}, "Expanded AllRows", JoinKind.LeftOuter),
#"Expanded Expanded AllRows" = Table.ExpandTableColumn(#"Merged Queries", "Expanded AllRows", {"Index4"}, {"Index4"})
in
#"Expanded Expanded AllRows"

The 3rd line  (#"Changed Type" =  ....) give me an error but I must delete that step and works perfect.

I really apreciate your explanation, example, code, time, help, etc.

Best regards and thank's

[AlexisOlson]

It gives an error since it's referring to a previous step "Source". When you deleted that step, it automatically re-linked the reference to #"Changed Type" in the #"Grouped Row" step to reference Critera_Table instead, making everything work. 🙂

Notice how each line references the prior one as the first argument in the table function and you'll have a better understanding of how to fix reference errors.

[Danielecc]

Thanks again for the explanation 👍🏼