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

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    Dasbas
    Frequent Visitor
    9 years ago

    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's avatar
      LaurentCouartou
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      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?