cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

find min and max by group and continuous order

using DAX how to calculate min and max for this table, based on the attribute color and they have to be in the same sequence , for example red -3 and red -6 are not considered in the same group as 3 and 6 are not in a continuous orderorder by.JPG

1 ACCEPTED SOLUTION
Super User II
Super User II

Hi @mim 

 

Please see the attached file with the solution.

The file contains
Calculated column

rankColorGroup = 
VAR __previousColor = 'Table'[color]
RETURN
    RANKX (
        FILTER ( 'Table', 'Table'[color] <> __previousColor ),
        'Table'[order],,,
        DENSE
    )

Two Measures

min = 
CALCULATE(
    MIN( 'Table'[order] ),
    ALL( 'Table' ),
    VALUES( 'Table'[rankColorGroup] )
)
max = 
CALCULATE(
    MAX( 'Table'[order] ),
    ALL( 'Table' ),
    VALUES( 'Table'[rankColorGroup] )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

View solution in original post

2 REPLIES 2
Super User II
Super User II

Hi @mim 

 

Please see the attached file with the solution.

The file contains
Calculated column

rankColorGroup = 
VAR __previousColor = 'Table'[color]
RETURN
    RANKX (
        FILTER ( 'Table', 'Table'[color] <> __previousColor ),
        'Table'[order],,,
        DENSE
    )

Two Measures

min = 
CALCULATE(
    MIN( 'Table'[order] ),
    ALL( 'Table' ),
    VALUES( 'Table'[rankColorGroup] )
)
max = 
CALCULATE(
    MAX( 'Table'[order] ),
    ALL( 'Table' ),
    VALUES( 'Table'[rankColorGroup] )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

View solution in original post

Hi, I tried to use solution proposed but when individual values are one after another there is an error in calculation. Do you have some idea how to fix this, I have no idea how to fix this.

 

RANKX.jpg

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.