SUMMERIZE with distinct values and condition

[OmarSaleh]

Hello 

Is there anyway to create a new table from an existing one taking distict values from one colum with max value of another one?

Table is something like this: 

The result table should contain distict ID values in column ID, with the Max value in column Value. 

Any idea? 

Thanks!

[v-xulin-mstf]

Hi @OmarSaleh

Is your issue solved?

If the issue has been solved, please adopt the solution to help others.

If you still have some question, please don't hesitate to let me known.‌‌

😉

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf · Accepted solution]

Hi @OmarSaleh,

You use Maxx function:

Measure = 
var _max=
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[ID]=MAX('Table'[ID])
    ),
    'Table'[Value]
)
return
IF(
    _max=MAX('Table'[Value]),
    1,
    0)

Here is the output:

The pbix is attached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Pragati11]

Hi @OmarSaleh ,

You can write a DAX as follows for creating a new table:

groupedTable = SUMMARIZE(tablename, tablename[ID], "Date", MAX(tablename[Date]), "Value", MAX(tablename[Value])

Thanks,

Pragati

[AlexisOlson]

@OmarSaleh, note that this takes the max of Date and Value independently, which could potentially produce rows that aren't from your original table if the maximal values for that ID occur in separate rows.

A different approach that pulls the Date corresponding to the maximal Value uses two steps:

ADDCOLUMNS (
    ADDCOLUMNS (
        DISTINCT ( Table1[ID] ),
        "Value", CALCULATE ( MAX ( Table1[Value] ) )
    ),
    "Date", CALCULATE ( MAX ( Table1[Date] ), Table1[Value] = EARLIER ( [Value] ) )
)

This pulls the date corresponding to the maximal value rather than the maximal date for that ID.