Custom column Index by two columns group

[tiagomarciano]

Hello people good night!

I have the following challenge. I would like to apply a sequential index to two groups of information, they are:

- Category;

- Date;

The image below details the objective (in yellow).

I believe a possible solution would be to apply a grouping by Category and Date in a referenced table, add an index column, and again expand the table. In these last steps I am not getting the desired result. The sequential index should be initialized to each new category as demonstrated in the second image.

Thanks in advance to anyone who can collaborate with this challenge, thank you very much.

[rachnajn28]

Using same calculated column formula in powerbi ,the result i am getting is

 

 its jumping values, please help. Sorting is proper.Thanks

[Ashish_Mathur · Accepted solution]

Hi @tiagomarciano,

Try this calculated column formula

=CALCULATE(DISTINCTCOUNT(Data[Data]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Data]<=EARLIER(Data[Data])))

[rachnajn28]

I have used this in powerbi 

I have articlecode and mrindate sorted in Asc, using this calculate column formula but some indexcol values are jumping

[tiagomarciano]

@Ashish_Mathur tks for your help, with the calculated column could use in the line chart.

[Ashish_Mathur]

You are welcome.

[MarcelBeug]

For a Power Query solution, you need a double "Group By":

1. On "Categoria" and within these nested tables:

2. On "Data".

The Index can be added to the inner group; once the inner nested tables are expanded, you get the duplicated Index values (same date, same Index value).

After expanding the nested tables per "Categoria", you get the sets of Index values per Categoria.

In the query below, function InnerGroupAddIndex is created for the inner grouping (which also adds the Index and expands the inner nested tables).

Function Value.Type is used twice to make sure that the nested tables have the correct column types.

This is an adjustment of the code that is generated when using "Group By" with operation "All Rows": that code only has "type table", which will reset all column types to "any". The first Value.Type is taken from table "Sorted Rows" with dummy column "Index" added (at this point, this is only required for the column type).

It is a rather complex solution.

let
    Source = Table1,
    #"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Categoria", Order.Ascending}, {"Data", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Categoria"}, {{"AllData", each InnerGroupAddIndex(_), Value.Type(Table.AddColumn(#"Sorted Rows", "Index", each 1, Int64.Type))}}),

    InnerGroupAddIndex = (Table as table) as table =>
    let
        #"Grouped Rows" = Table.Group(Table, {"Data"}, {{"AllData", each _, Value.Type(Table)}}),
        #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
        #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"Categoria"}, {"Categoria"})
    in
        #"Expanded AllData",

    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Data", "Index"}, {"Data", "Index"})
in
    #"Expanded AllData"

[aviral]

Hi @MarcelBeug: 

That solution helped me in grouping at three nested levels and rank the rows.

Really thankful for that. 

I am now thinking of converting it to a reusabe function. 

Would there be any issues with schedule-refresh if we use functions? 

[BILASolution]

Hi @tiagomarciano

Try this calculated column

Index Column = 

var actualcat = FIRSTNONBLANK(Category[Categoria];1)
var actualdate = FIRSTNONBLANK(Category[Data];1)

return

CALCULATE
(
    DISTINCTCOUNT
    (
        Category[Data]
    );ALL(Category);Category[Categoria] = actualcat;Category[Data] <= actualdate
)

I hope this helps

Regards

BILASolution