Group By +Index creation in DAX

[chanal]

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

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 !

[amitchandak]

@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

[chanal]

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 🙂

[harshnathani]

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)

[harshnathani]

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)

[chanal]

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 :

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) 

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

Thank you very much

Kind regards

[harshnathani · Accepted solution]

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)

[chanal]

Hi @harshnathani 

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 🙂