Forum Discussion

TL5866's avatar
TL5866
Frequent Visitor
8 years ago
Solved

FILTERS() vs VALUES()

Hi,

 

DAX has two functions that can be used as filter in CALCULATE: VALUES() and FILTERS().

Would anyone know how they really differ - apart from the fact that VALUES() also takes a table as parameter ? I have done a few tests, but could not spot a difference in results...

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    TL5866

     

    They have difference. FILTERS() will directly apply the column as filter into your calculation, which will ingore other filter. But for VALUES(), it just returns a table of single column with distinct values, which means it will take other filter as well. Please see my sample below: 

     

    Measure with VALUES = COUNTROWS(VALUES('Table'[YearMonth]))
    Measure with FILTERS = COUNTROWS(FILTERS('Table'[YearMonth]))

     

    Regards, 

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    TL5866

     

    They have difference. FILTERS() will directly apply the column as filter into your calculation, which will ingore other filter. But for VALUES(), it just returns a table of single column with distinct values, which means it will take other filter as well. Please see my sample below: 

     

    Measure with VALUES = COUNTROWS(VALUES('Table'[YearMonth]))
    Measure with FILTERS = COUNTROWS(FILTERS('Table'[YearMonth]))

     

    Regards,