Forum Discussion
Use values of variables to filter data
Hello,
I have 2 variable like as bellow :
Var1 = SUMMARIZE(
ORDDTS,
ORDDTS[ID_CUSTOMER]
, "MINORDER",MIN(ORDDTS[ID_ORDERDATE])
)
Var2 = SUMMARIZE(
ORDDTS,
ORDDTS[ID_CUSTOMER]
, "MAXORDER",MAX(ORDDTS[ID_ORDERDATE])
)
I want to filter the customer who have MaxOrder-MinOrder>100
i did the code as bellow but doesn't work
CALCULATE(NATURALINNERJOIN(var1, var2), VALUES('var1'[MINORDER]) - VALUES('var2'[MAXORDER])>100)
Any idea how can i do that ?
Thanks for help !
Hi Anonymous
Not sure what exactly are trying to achieve. I guess this is a 100 days difference between first and last orders. Please try
Table1 = VAR T1 = ADDCOLUMNS ( SUMMARIZE ( ORDDTS, ORDDTS[ID_CUSTOMER] ), "@MINORDER", CALCULATE ( MIN ( ORDDTS[ID_ORDERDATE] ) ), "@MAXORDER", CALCULATE ( MAX ( ORDDTS[ID_ORDERDATE] ) ) ) VAR T2 = FILTER ( T1, [@MAXORDER] - [@MINORDER] = 100 ) RETURN T2
3 Replies
- tamerj1
Community Champion
Hi Anonymous
Not sure what exactly are trying to achieve. I guess this is a 100 days difference between first and last orders. Please try
Table1 = VAR T1 = ADDCOLUMNS ( SUMMARIZE ( ORDDTS, ORDDTS[ID_CUSTOMER] ), "@MINORDER", CALCULATE ( MIN ( ORDDTS[ID_ORDERDATE] ) ), "@MAXORDER", CALCULATE ( MAX ( ORDDTS[ID_ORDERDATE] ) ) ) VAR T2 = FILTER ( T1, [@MAXORDER] - [@MINORDER] = 100 ) RETURN T2