Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Ashish_Mathur
Super User
Super User

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

8 REPLIES 8
rachnajn28
Frequent Visitor

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

16954471285476630539877170296823.jpg

16954471837834087138516028248443.jpg

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

Ashish_Mathur
Super User
Super User

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/

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

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

You are welcome.


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

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)

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.