Want to create a calculatetable by taking max values

[sarjensystems1]

Hello All,

I want to create a calculate-table by taking max values of 'indirectcontractpricetoday' for every 'Rebate Key'. 

I have used maxx function to create the same but it's gave error like we cannot use maxx function in filter section.

Thanks in advance.

[Fowmy]

@sarjensystems1 

Can you try this as a new table from the Modeling Tab:

Table1 = 
SUMMARIZE(
    'Rebate Copy',
    'Rebate Copy'[Reate Key],
    "Maxvalue", MAX( 'Rebate Copy'[indirectcontractpricetoday])
)

[Fowmy]

@sarjensystems1 

Do not use CALCULATEDTABLE function. Just use the CODE as I shared.

[sarjensystems1]

Hi @Fowmy 
 
Sorry about that, I got your Point, But in my case i want whole Table 'Rebate copy' for my later calculations, that's why i am looking for a dax that should create calculate table with max value.

[Fowmy]

@sarjensystems1 

If you need the whole table then where do you want to show the max value ?

Do you need a new column that shows the max value for each Key with the new table ? It will repeat

[sarjensystems1]

hi @Fowmy 

Is it possible to get the max values of 'indirectcontractpricetoday' in itself. Because I just want a table where for every rebatekey there is only one row , i.e. maximum value of  'indirectcontractpricetoday'.

[Fowmy · Accepted solution]

@sarjensystems1 

You can try this version:

Table 1 = 
FILTER(
    ADDCOLUMNS(
        'Rebate Copy',
        "Maxvalue", 
            VAR __MAX = CALCULATE( MAX('Rebate Copy'[indirectcontractpricetoday]) , ALLEXCEPT( 'Rebate Copy' , 'Rebate Copy'[Reate Key]] )) 
                RETURN
                IF( 'Rebate Copy'[indirectcontractpricetoday] = __MAX , __MAX)
    ),
    [Maxvalue] <>BLANK()
)
   

[sarjensystems1]

Hi @Fowmy 

Thanks for that. It worked .😊

[Fowmy]

@sarjensystems1 

May be you need the following table with all the records:

Measure = 
ADDCOLUMNS(
     'Rebate Copy',
    "Maxvalue", CALCULATE(  MAX( 'Rebate Copy'[indirectcontractpricetoday]) , ALLEXCEPT(  'Rebate Copy' ,  Rebate Copy'[Reate Key] ))
)

[sarjensystems1]

Hi @Fowmy 

According to your dax , i am getting new column named 'maxvalue' having two rows with same value i.e. "31.05".

But I only want a single row where 'indirectcontractprice' = MAX for every rebate key. Attaching screenshot FYR.

I just want first row in my calculated or new table. 

[sarjensystems1]

Hi @Fowmy 

Still getting both values in 'indirectcontractpricetoday' for filtered rebate key.

[amitchandak]

@sarjensystems1 , How are you creating that ?

example

summarize( Table, Table[Date], "indirectcontractpricetoday", max(Table[indirectcontractpricetoday]))

[sarjensystems1]

Hi @amitchandak 

I have used the dax as mentioned by you, but can you please suggest what should i pass in summarize function as a third paramter, Because I want row with a maximum value of 'indirectcontractpricetoday' for every single 'distinct key'.