DAX Studio and Filter Context

[mmann02]

Hello,

I've read lots of documentation regarding filter context, but to make it concrete my desire is to be able to play around with the various filter and table functions to see what happens.

Inside Power BI Desktop it doesn't appear that I'm able to see non-aggregated results of DAX formulas that return tables.  That is unless I'm missing something.

With that in mind I turned to DAX Studio, but it doesn't appear to be able to execute a query inside a filter context based on slicers within a Power BI report.  It also doesn't appear to be possible to manually re-create the Power BI filter context in DAX Studio.  Again, unless I'm missing something.

Any help or pointers is much appreciated.

[TomMartens · Accepted solution]

Hey,

it's possible to recreate the filter context in DAX Studio, but before you start trying different things it's necessary understand the following:

• each slicer selection is translated into a table, this table will be used tablefilter, as a side note, this explains why every filter in  DAX is a table.
  
  

  CALCULATE(
  SUM('fact sale'[quantity])
  ,'dimension city'[sales territory] = "Plains"
  )

  Is basically this
  
  

  calculate(
  sum('fact sale'[quantity]
  ,filter(all('dimension city'[sales territory]), [sales territory] = "Plains")
  )

  You have to note that filter returns a table, even if this table just has one column.

this is a DAX query traced by DAX Studio:

DEFINE VAR __DS0FilterTable = 
  TREATAS({"Plains"}, 'Dimension City'[Sales Territory])

EVALUATE

    SUMMARIZECOLUMNS(
      'Dimension City'[State Province],
      __DS0FilterTable,
      "SumQuantity", CALCULATE(SUM('Fact Sale'[Quantity]))
    )

Hopefully this provides some insights that you might missing.

Regards,

Tom

[Richard_100]

@TomMartens 

Appreciate this is reviving an old post, but I was hoping you might be able to add more to your answer here please?

I also would like to recreate the filter context in DAX, to see the underlying table calculation results as executed in the filter context of my report.  Using your method above, I can recreate the filter context of the one column, but not multiple columns (my matrix has three columns, for example), and only for the SUMMARIZECOLUMNS as above

I use the pattern of ADDCOLUMNS(SUMMARIZE()) a lot, creating an interim table in a variable for use later in a calculation, but sometimes the filter context is too complex for me to be sure exactly what that interim table is returning, making it really hard to think through how the rest of my calculation plays out

I'd really appreciate a simple pattern to copy that allows me to recreate a multiple-column, multiple-value filter context in DAX Studio.  

Thanks in advance (to Tom, or anyone else who might step in)

Regards

Richard

[TomMartens]

Hey @Richard_100 ,

please post the DAX statement that you want to analyze more thoroughly by using the Performance Analyzer in Power BI Desktop.

Regards,
Tom

[mmann02]

That is precisely the insight I was missing.  Lots of things are already considerably more clear.  Thank you!

[mmann02]

Awesome.  Thanks for the response.  I'll be diving into this more tomorrow!