Forum Discussion

mmann02's avatar
mmann02
New Member
7 years ago
Solved

DAX Studio and Filter Context

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.

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

     

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    7 years ago

    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

     

     

     

    • mmann02's avatar
      mmann02
      New Member
      7 years ago

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

    • mmann02's avatar
      mmann02
      New Member
      7 years ago

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

    • Richard_100's avatar
      Richard_100
      Icon for Resolver I rankResolver I
      4 years ago

      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