Forum Discussion

kabra_ashish's avatar
kabra_ashish
Icon for Helper III rankHelper III
6 years ago
Solved

Finding MAX in each category

Need some quick help.   I have a table in my data model as below:   Record       Name           Dates                Transactions (#) 1                   A              Jan-01-2020              ...
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    You could try this as a calc column:

    IsMax - IF( 'Table1'[Transaction] a MAXX(FILTER(ALL('Table1'), 'Table1'[Name] ? EARLIER('Table1'[Name])), 'Table1'[Transaction]), "Yes", "No")
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago
You could try this as a calc column:

IsMax - IF( 'Table1'[Transaction] a MAXX(FILTER(ALL('Table1'), 'Table1'[Name] ? EARLIER('Table1'[Name])), 'Table1'[Transaction]), "Yes", "No")
kabra_ashish's avatar
kabra_ashish
Icon for Helper III rankHelper III
6 years ago

All the solutions provided by  AllisonKennedy mahoneypat Anonymous have actually worked.

 

Can I add one more similar question to it? This will remove the unnecessary tables and improve the data model.

 

Lets say I have just 1 field:

 

Field:

A01/01/2020

A01/01/2020

A01/01/2020

A03/12/2020

B02/11/2020

B03/29/2020

 

Is it possible to add a new calculated column which give the counts:

Example: records 1,2 and 3 will give 3 each because they belong to the same category (A01/01/2020) and then 4,5,6 gives 1 each.  

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI kabra_ashish ,

     

    Create a measure = CALCULATE(COUNTA(Table[id]) , FILTER(ALL(Table) , Table[id] = MAX(Table[id]))

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI kabra_ashish ,

       

      You can also create a colun

       

      Column = COUNTX(FILTER('Table', 'Table'[Id] = EARLIER('Table'[Id])),'Table'[Id])

       

      \

       

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

  • kabra_ashish's avatar
    kabra_ashish
    Icon for Helper III rankHelper III
    6 years ago

    Thank you Anonymous. This is really amazing!

     

    Sorry too many questions but this is my last one (which is basically a combination of my first and previous question) - In the below example A01/01/2020 has the highest count (appearing thrice) so I need a "Yes" in front of them. Simlarly B has the highest on 24/02/2020 (appearing twice). If you can help me here as well that would be really helpful so that I can close my series of questions.

     

    Name:                        Column (needed)

    A01/01/2020                  Yes

    A01/01/2020                  Yes

    A01/01/2020                  Yes

    A15/03/2020                  No

    A20/04/2020                  No

    B12/01/2020                  No

    B24/02/2020                  Yes

    B24/02/2020                  Yes

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi kabra_ashish ,

       

      You will need to separate A and B in a separate Column.

       

       

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

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi kabra_ashish ,

      You can create this calculated column:

      Result = 
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Count", CALCULATE ( COUNT ( 'Table'[Name] ), FILTER(ALL('Table'),'Table'[Name] = EARLIER ( 'Table'[Name] ) )),
        "_Name", LEFT ( 'Table'[Name], 1 )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Re",
        VAR _max =
            MAXX ( FILTER ( tab, [_Name] = EARLIER ( [_Name] ) ), [Count] )
        RETURN
            IF ( [Count] >= _max, "Yes", "No" )
    )
RETURN
    MAXX ( FILTER ( newtab, [Name] = EARLIER('Table'[Name]) ), [Re] )

      Attached a sample file in the below, hopes to help you.

       

      Best Regards,
      Yingjie Li

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.