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.  ...
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

     

     

     

TomMartens's avatar
TomMartens
Super 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
    Resolver 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