Forum Discussion

tiagomarciano's avatar
tiagomarciano
Frequent Visitor
8 years ago
Solved

Custom column Index by two columns group

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.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi tiagomarciano,

     

    Try this calculated column formula

     

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

     

8 Replies

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi tiagomarciano,

     

    Try this calculated column formula

     

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

     

    • MarcelBeug's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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's avatar
        aviral
        Icon for Advocate IV rankAdvocate IV
        8 years ago

        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? 

         

    • rachnajn28's avatar
      rachnajn28
      Frequent Visitor
      2 years ago

      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

  • rachnajn28's avatar
    rachnajn28
    Frequent Visitor
    2 years ago

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

     

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