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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors