Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

@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

Helper I

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 🙂

Community Champion

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)

Community Champion

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)

Helper I

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)

Thank you very much

Kind regards

Community Champion

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)

Helper I

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors