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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
chanal
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

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

 

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 🙂

 

 

 

 

 

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)

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)

 

 

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

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)

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors