locked
writing dax query RRS feed

  • Question

  • Hello. I have copied a linkback table into excel. How do I return all columns but only the rows that have values over $1,000 in the [Expensive Claims] column. 
    Thursday, October 20, 2016 7:24 PM

Answers

  • The following pattern should work

    EVALUATE FILTER( '<your table name>',  '<your table name>'[Expense Claims] > 1000)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by AlexMartini Sunday, October 23, 2016 10:05 AM
    Friday, October 21, 2016 12:41 AM

All replies

  • The following pattern should work

    EVALUATE FILTER( '<your table name>',  '<your table name>'[Expense Claims] > 1000)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by AlexMartini Sunday, October 23, 2016 10:05 AM
    Friday, October 21, 2016 12:41 AM
  • Hi AlexMartini,

    I try to reproduce your scenario and get the expected result.

    The following table in a linkback table in Excel 2016 version. In Home window, click Format as tale. You will see an inverted triangle(highlighted in yellow) in header of every column.

     
    Click the drop-down arrow in yellow of the column you want to filter. If the column has numbers, click Number Filters. If the column has text entries, click Text Filters. It returns below second navigation.

     
       
    Then enter the threshold to red circle, click OK. You will get the filtered table. Finally, please copy the filtered and paste to new one like below screenshot.
     

    Best Regards,
    Angelia
    Friday, October 21, 2016 9:17 AM
  • Thanks Darren. How do I learn that DAX code (that works on tables as opposed to regular power pivot) and what is it useful for outside of that? 
    Sunday, October 23, 2016 10:06 AM
  • How do I learn that DAX code (that works on tables as opposed to regular power pivot) 

    It's the same DAX you just use EVALUATE <table expression>. These are the same table expressions that you would use in something like a SUMX(). There is documentation on the Microsoft MSDN site or you could search for "DAX Query" or "DAX EVALUATE" to find blog articles. I think the book "The Definitive Guide to DAX" might have a section on querying.

    and what is it useful for outside of that? 

    It's probably less useful in a Excel PowerPivot environment. You can use it for extracting data or for performance tuning exercises. But going against Power BI or Analysis Services these DAX queries can be used to provide data sets for other reporting tools.


    http://darren.gosbell.com - please mark correct answers

    Monday, October 24, 2016 2:58 AM