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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Fabiopb
Frequent Visitor

Calculate a Max value for differente matriculates

Hello!

 

I have a table with different values of diameter for different matricules. I need to calculate which is the max diameter for each matricule and after that calculate how many times that diameter appears for that matricule. How could I do it with a calculated column and/or a meassure? I thougth of creating a second table where I have a column with unique matricules values then create a second column with = Calculate(MAX(Diameter[Table1]), Matricules[Table1] = Matricules[Table2]) and a third column = CALCULATE(COUNT(Diameter[Table1]), Matricules[Table1] = Matricules[Table2],Diameter[Table1] = MAX(Diameter[Table1]). That was my idea, but I don´t know how to write that. Could you help me please? I am a rookie 

1 ACCEPTED SOLUTION

Hi @Fabiopb 

 

Sorry for the late reply. You can first create a table with below code. This will return you a table with distinct matricules and their maximum diameter values. 

Table 2 = SUMMARIZE('Table','Table'[Matricule],"Max Diameter",MAX('Table'[Diameter]))

vjingzhang_0-1654054945495.png

 

Then add a new column in above new table. 

Max Diameter Count = 
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Matricule] = 'Table 2'[Matricule]
            && 'Table'[Diameter] = 'Table 2'[Max Diameter]
    )
)

vjingzhang_1-1654055234188.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @Fabiopb 

 

Here is one solution with calculated columns. Create two columns

Matricule Max Diameter = CALCULATE(MAX('Matricules'[Diameter]),ALLEXCEPT(Matricules,Matricules[Matricule]))
Max Count = CALCULATE(COUNT(Matricules[Matricule]),ALLEXCEPT(Matricules,Matricules[Matricule]),Matricules[Diameter]=Matricules[Matricule Max Diameter])

vjingzhang_0-1653621931314.png

 

And here is one solution with a measure based on above sample data. 

Measure = 
var matricule_max_diameter = MAX(Matricules[Diameter])
return
CALCULATE(COUNT(Matricules[Matricule]),Matricules[Diameter]=matricule_max_diameter)

vjingzhang_1-1653622156642.png

 

You can choose either per your need. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hello,

 

Thank you for the response. I am actually looking for a solution like this:

 

Fabiopb_0-1653653900568.png

 

I would prefer to calculate the new columns in a new table with the matricules not repeated. I am looking for a solution like that because the main table has a lot of rows which I believe including new calculated columns would be worse for the structure. If you could help with that I would appreciate it.

Hi @Fabiopb 

 

Sorry for the late reply. You can first create a table with below code. This will return you a table with distinct matricules and their maximum diameter values. 

Table 2 = SUMMARIZE('Table','Table'[Matricule],"Max Diameter",MAX('Table'[Diameter]))

vjingzhang_0-1654054945495.png

 

Then add a new column in above new table. 

Max Diameter Count = 
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Matricule] = 'Table 2'[Matricule]
            && 'Table'[Diameter] = 'Table 2'[Max Diameter]
    )
)

vjingzhang_1-1654055234188.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.