cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
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

 

 

1.jpg

 

 

Regards,
Harsh Nathani

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

View solution in original post

7 REPLIES 7
Highlighted
Super User IX
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]))

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Group By +Index creation in DAX

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 🙂

 

 

 

 

 

Highlighted
Super User V
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
Super User V

Re: Group By +Index creation in DAX

Hi @chanal ,

 

You can try 

 

1.jpg

 

 

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
Helper I

Re: Group By +Index creation in DAX

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 resultmy 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

Highlighted
Super User V
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

 

 

1.jpg

 

 

Regards,
Harsh Nathani

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

View solution in original post

Highlighted
Helper I
Helper I

Re: Group By +Index creation in DAX

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 🙂

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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