topic Re: Group By +Index creation in DAX in DAX Commands and Tips

Group By +Index creation in DAX

chanal — Wed, 05 Aug 2020 23:01:25 GMT

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 :

PieceRefNumber

514

515

714

1020

The result I want is :

1 2 3 4

Groupby_PieceRefNumber Index_PieceRef Nb Occurence Difference

condition => filter no 0 condition : start with (1-2)

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 !

Re: Group By +Index creation in DAX

amitchandak — Thu, 06 Aug 2020 02:51:39 GMT

chanal , Create a table like

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

and add these new columns

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

diff = [PieceRefNumber]- [Index_PieceRef]

You can combine all these three using add columns

https://docs.microsoft.com/en-us/dax/addcolumns-function-dax

Re: Group By +Index creation in DAX

chanal — Thu, 06 Aug 2020 14:13:13 GMT

Hello amitchandak

Thank you for your answer.

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 🙂

Re: Group By +Index creation in DAX

Anonymous — Thu, 06 Aug 2020 13:11:47 GMT

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)

Re: Group By +Index creation in DAX

Anonymous — Thu, 06 Aug 2020 13:40:24 GMT

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)

Re: Group By +Index creation in DAX

chanal — Thu, 06 Aug 2020 14:38:32 GMT

Hello harsh

Thank you for your answer.

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

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

https://drive.google.com/file/d/1S16sD-BCT9uBkKLyiuEueJCEEUSd38R-/view?usp=sharing

Thank you very much

Kind regards

Re: Group By +Index creation in DAX

Anonymous — Thu, 06 Aug 2020 16:25:31 GMT

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)

Re: Group By +Index creation in DAX

chanal — Thu, 06 Aug 2020 17:30:15 GMT

Hi Anonymous

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

i give the link with the successful example file : https://drive.google.com/file/d/1jfjgozpaCrIFsCeejF2VfuQrxW1HUtUi/view?usp=sharing

A real Champion 🙂