Forum Discussion

Dasbas's avatar
Dasbas
Frequent Visitor
9 years ago
Solved

DAX equivalent to this.var

To compare the date of a specific row to the rest of the dates in that column, in a calculated column. I need some kind of this.var function as we know from Java   Thanks!
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Dasbas

     

    According to your description, you should be able to use VAR function to store the current row date as a named variable, then use FILTER function to iterate through all the other values of that column and compare them to the stored variable. See my sample below.

     

    Assume we have table called "MyTestTable" like below, now we need to create a calculate column to compare the date of current row to the rest of dates in that column and calculate the count of them which is smaller than the current row date.

    Then you should be able to use the formula below to create the calculate column.

    Column = 
VAR currentRowDate = MyTestTable[Date]
RETURN
    CALCULATE (
        COUNTA ( MyTestTable[Date] ),
        FILTER ( ALL ( MyTestTable ), MyTestTable[Date] < currentRowDate )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Dasbas

 

According to your description, you should be able to use VAR function to store the current row date as a named variable, then use FILTER function to iterate through all the other values of that column and compare them to the stored variable. See my sample below.

 

Assume we have table called "MyTestTable" like below, now we need to create a calculate column to compare the date of current row to the rest of dates in that column and calculate the count of them which is smaller than the current row date.

Then you should be able to use the formula below to create the calculate column.

Column = 
VAR currentRowDate = MyTestTable[Date]
RETURN
    CALCULATE (
        COUNTA ( MyTestTable[Date] ),
        FILTER ( ALL ( MyTestTable ), MyTestTable[Date] < currentRowDate )
    )

 

Regards

  • Dasbas's avatar
    Dasbas
    Frequent Visitor
    9 years ago

    v-ljerr-msft Thanks man, the var thing worked. Based on your answer I was able to write the querry i needed.

     

    ActiveAtTime = 
    VAR currentRowDate = Calls[Closure Date] 
    RETURN 
    CALCULATE(
    COUNTA(Calls[Call Date]);
    FILTER(ALL(Calls);Calls[Closure Date] > currentRowDate); 
    FILTER(ALL(Calls);Calls[Call Date] < currentRowDate)
    )

    Thanks a lot!