Forum Discussion
Use another Function instead MAX() in a Calculate
Hi everyone,
I have 2 tables, the one is a SALE table in which I have the contract Number, Price, QTY etc.
Thr other table is the CORRECTION TABLE, in which I track every amendment that that happened in the Sales table for PRICE and QTY. There I have the following fields.
CONTRACT, FIELD, OLD_VALUE, NEW_VALUE
I would like to use the TREATAS() function in order to retrieve this information in a visual in PBI, since my model is a bit complicated and due to other relationships I can't link these 2 tables.
So I created the following measure but with the MAX(), I am losing data since I am taking always the greater value.
Is there any way to retrieve all the values by using another function instead of MAX.
MY MEASURE=
CALCULATE (
MAX([FIELD/OLD_VALUE/NEW_VALUE]),
TREATAS(VALUES(SALES[CONTRACT]),CORRECTION[CONTRACT])
Hi lefo1911 ,
I created some data:
CORRECTION：
SALES：
Here are the steps you can follow：
1. Create measure.
Field_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[FIELD])Newvalue_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[NEW_VALUE])Oldvalue_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[OLD_VALUE])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi lefo1911 ,
I created some data:
CORRECTION：
SALES：
Here are the steps you can follow：
1. Create measure.
Field_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[FIELD])Newvalue_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[NEW_VALUE])Oldvalue_measure = VAR _select=SELECTCOLUMNS('SALES',"1",[CONTRACT]) return MAXX(FILTER(ALL('CORRECTION'), 'CORRECTION'[CONTRACT] in _select&&'CORRECTION'[CONTRACT]=MAX('CORRECTION'[CONTRACT])),[OLD_VALUE])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly