Forum Discussion

kabra_ashish

Helper 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
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

Helper 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

A15/03/2020 No

A20/04/2020 No

B12/01/2020 No

B24/02/2020 Yes

v-yingjl

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

Finding MAX in each category.pbix34 KB