cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tiagomarciano
Frequent Visitor

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).

 

rankIndex_help.JPG

 

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.

 

rankIndex_help_exemplo.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Custom column Index by two columns group

Hi @tiagomarciano,

 

Try this calculated column formula

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
BILASolution Solution Specialist
Solution Specialist

Re: Custom column Index by two columns group

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
)

aaa.png

 

I hope this helps

 

Regards

BILASolution

Super User IV
Super User IV

Re: Custom column Index by two columns group

Hi @tiagomarciano,

 

Try this calculated column formula

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

MarcelBeug Community Champion
Community Champion

Re: Custom column Index by two columns group

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"

 

Specializing in Power Query Formula Language (M)
tiagomarciano
Frequent Visitor

Re: Custom column Index by two columns group

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

Highlighted
Super User IV
Super User IV

Re: Custom column Index by two columns group

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
aviral Advocate II
Advocate II

Re: Custom column Index by two columns group

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? 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors