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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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