Usage of VAR

[calimero48]

Hi 

I want to created a calculated column bases of rows contents.

The DAX command

EVALUATE
Var _column1 ="1000"
Var _column2= "10"
Var _internaltable=SUMMARIZECOLUMNS(
            Table1[Coumn1],
            Table1[Column2],
            FILTER(ALL(Table1),Table1[column1]=_column1 && Table1[Column2]=VALUE(_column2)),
            "date1",Min(Table1[Date1]),
            "date2",Min(Table1[Date2]))
var _date1 = ROW("date1",GROUPBY(_internaltable,"date1",MINX(CURRENTGROUP(),[date1)))
var _date2 = ROW("date2",GROUPBY(_internaltable,"date2",MINX(CURRENTGROUP(),[date2])))
RETURN 
    IF (ISBLANK(_date1),BLANK(),_date2)

The IF statement return the error "the request expression is not a valid table".

How can I do logic with the variable calculated before ( The IF will be more complex ...)

Thanks for your help

Regards

[lbendlin]

There are a couple of typos in your code but I think it starts breaking down here

FILTER(ALL(Table1),Table1[column1]=_column1 && Table1[Column2]=VALUE(_column2)),

The VALUE() part is missing for _column1

It feels like your approach at the issue is a bit complex. What are you actually trying to achieve?

[calimero48]

Hi

I need to create a column with a complex logic over milions of records.

The first step internaltable get all the needed values I will use to calculate the return value.

After that i will do some logic like:

   1 date1 et date2 not blank or not equal to a value

   2 if date 1 < date2 calculate the number of working day like

      CALCULATE(SUM(dimdate[IsWorkingDay])-1,
                            DATESBETWEEN(dimdate[FullDateAlternateKey],
                            date1,
                            date2))

The error is on VAR that is saying it's not a table even if I use ROW.

I have tried a simpler formula and I got the same error.

EVALUATE
    Var _test="Test"
    RETURN IF(ISBLANK(row("test",_test)),"Blank","Something")

I think I miss something with var ...

If somebody can help

Regards

[lbendlin · Accepted solution]

EVALUATE needs a table as a result.

So instead of 

EVALUATE
    Var _test="Test"
    RETURN IF(ISBLANK(row("test",_test)),"Blank","Something")

you should do 

EVALUATE 

ROW("test",
    Var _test="Test"
    RETURN IF(ISBLANK(row("test",_test)),"Blank","Something")

)

[AnkitKukreja]

Hi @calimero48 

You can use If (isblank ) inside _date1 something like:

var _date1 = If (isblank ( ROW("date1",GROUPBY(_internaltable,"date1",MINX(CURRENTGROUP(),[date1))) , Blank() ,
ROW("date2",GROUPBY(_internaltable,"date2",MINX(CURRENTGROUP(),[date2])))

Return

_date1

Hope it helps.

Thanks,

Ankit