Forum Discussion

calimero48's avatar
calimero48
Icon for Helper II rankHelper II
6 years ago
Solved

Usage of VAR

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's avatar
    lbendlin
    6 years ago

    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")

    )

4 Replies

  • AnkitKukreja's avatar
    AnkitKukreja
    Icon for Super User rankSuper User
    6 years ago

    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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      calimero48
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        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")

        )