DAX equivalent to this.var

[Dasbas]

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!

[v-ljerr-msft · Accepted solution]

@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]

@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!

[ankitpatira]

@Dasbas You can add Index column to your table and then use below code to compare specific row date to the rest of the column. 

Index,Date

1,01/01/2016

2,02/01/2016

3,03/01/2016

4,04/01/2016

5,05/01/2016

6,06/01/2016

First create measure that gives you specific row date (as example getting date of row with index value of 5).

Measure = LOOKUPVALUE(TABLENAME[Date],TABLENAME[Index],5)

Then create calculated column as below,

Column = IF(TABLENAME[Date] = [Measure],"matched","unmatched")

[Dasbas]

@ankitpatira Thank you, that's a great concept I didn't think about. I however can't use a measure with predefined index value since i need the check executed for each row, like in a loop.

So to clarify with pseudo code:

int index = 0;

foreach dates {

Measure = LOOKUPVALUE(TABLENAME[Date],TABLENAME[Index],index)

 Column = IF(TABLENAME[Date] = [Measure],"matched","unmatched")

index ++;

}

[LaurentCouartou]

You have been able to figure out the result should look something like that:

Column = IF(TABLENAME[Date] = SomeExpression ,"matched","unmatched")

The question is how you define SomeExpression.

Can you tell us more about how do you want to choose the other row? Can you show us some mockup data and what the results should look like?