cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Group By +Index creation in DAX

Hello,

I would like to do this command in DAX and not in M (too many datas)

My table : a list of number from 0

Before :

1

PieceRefNumber

0

514

514

515

515

714

714

714

1020

The result I want is :

1                                                  2                                        3                                            4

Groupby_PieceRefNumber       Index_PieceRef                Nb Occurence                   Difference

1st number in Groupby_

514                                               514                                     2                                        0

515                                               515                                     2                                        0

714                                               516                                     3                                      198

1020                                             517                                     1                                      503

Is it possible to do it in DAX : the function GROUP BY and INDEX creation ?

and if so, with calculated columns or measures please ?

Thank you so much for your help !

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V

## Re: Group By +Index creation in DAX

Hi @chanal ,

Use this measure

``````Index_PieceRef def =

MINX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PieceRefNumber] > 0
),
'Table 1'[PieceRefNumber]
)
+
RANKX (
FILTER (
ALL ( 'Table 1'),
'Table 1'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table 1'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1``````

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

7 REPLIES 7
Highlighted
Super User IX

## Re: Group By +Index creation in DAX

@chanal , Create a table like

Table1 = summarize(Table,Table[PieceRefNumber], "Nb Occurence",Count(Table[PieceRefNumber]))

Index_PieceRef = min(Table1[PieceRefNumber]) + rank(all(Table1), [PieceRefNumber],,asc,dense)

diff = [PieceRefNumber]- [Index_PieceRef]

You can combine all these three using add columns

Proud to be a Super User!

Highlighted
Helper I

## Re: Group By +Index creation in DAX

Hello @amitchandak

It has worked well with the formula Summarize.

But I have a problem with the formula Rank

PowerBI does not accept Rank only, but Rank.eq or Rankx

so I took the Rank.eq, and gives me this :

Table 1

PieceRef Number           Nb Occurence     Index_PieceRef

0                                           1                          5

514                                       2                          4

515                                       2                          3

714                                       3                          2

1020                                     1                          1

so, there are 2 problems :

1- The order is descending instead af ascending

2- It takes the number 0 into account, and i don't want that it begins with 0, but with 514

if I filter in the column "PieceRefNumber" => no 0, I have this :

Table 1

PieceRef Number           Nb Occurence     Index_PieceRef

514                                       2                          4

515                                       2                          3

714                                       3                          2

1020                                     1                          1

This is better, but it is not in the order acending and it begins with 1 and not 514.

I give you my example pbix file : https://drive.google.com/file/d/17fYuw_70Xb1B8sw1mdJzS1okbIl4ooK3/view?usp=sharing

Thank you so much 🙂

Highlighted
Super User V

## Re: Group By +Index creation in DAX

Hi @chanal ,

You need to provide Access.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Super User V

## Re: Group By +Index creation in DAX

Hi @chanal ,

You can try

Create Measures

``````Index_PieceRef =
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[PieceRefNumber] > 0
),
'Table'[PieceRefNumber]
)
+ RANKX (
FILTER (
ALL ( 'Table'[PieceRefNumber] ),
'Table'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1``````

``Differnce = MAX('Table'[PieceRefNumber]) - [Index_PieceRef]``

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Helper I

## Re: Group By +Index creation in DAX

Hello @harsh

I tried with your formulas and it is nearly there, because i don't have the same results as you :

my resultthe index begins with 515 and finish with 515

I researched why and i dont' see => can you look at the model please ? (The link access is now public)

Thank you very much

Kind regards

Highlighted
Super User V

## Re: Group By +Index creation in DAX

Hi @chanal ,

Use this measure

``````Index_PieceRef def =

MINX (
FILTER (
ALL ( 'Table 1' ),
'Table 1'[PieceRefNumber] > 0
),
'Table 1'[PieceRefNumber]
)
+
RANKX (
FILTER (
ALL ( 'Table 1'),
'Table 1'[PieceRefNumber] > 0
),
CALCULATE (
MAX ( 'Table 1'[PieceRefNumber] )
),
,
ASC,
DENSE
) - 1``````

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Helper I

## Re: Group By +Index creation in DAX

Yes, it works ! thank you so much for your help !

A real Champion 🙂

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors