Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmann02
New Member

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.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.