Remove Filter Context to create "materialized" table

[Anonymous]

Is there any way in DAX to create a derived table based on a filter expression and prevent any downstream calculations from ignoring the filter expression via functions like ALLEXCEPT?

Basically, users of the table must not be able to anyhow affect pre-filtering.

I would expect Power BI "new table" to behave like this, but I am not sure if it does.

[Anonymous]

@CNENFRNL , thx for confirmation!

@littlemojopuppy

Original trouble: power-bi page has multiple charts and all of them are using same source table, but there is one chart that uses subset of the table and the chart is a percentage-of-sub-total one. To calculate sub-total excluding one dimension I use ALLEXCEPT.

Then it depends on where I apply subset filter:

• on page\chart level: ALLEXCEPT overrides it
• anywhere upstream from the subtotal measure: ALLEXCEPT overrides it
• "new table" via Power-BI menu: works fine, but it feels odd to create new tables every time I have to use ALLEXCEPT (I even started prefixing them with underscore...); also it makes playing with filters on chart-level impossible

In DAX Studio it also could be helpful, for instance:

SUMMARIZE(
  FILTER(sessions, [next-preparing-group] <> "else"),
  [connector],
  [next-preparing-group],
  [L1]
)

I want to make this FILTER to be unoverridable to make my life easier. ALLEXCEPT happily overrides the filter in any downstream calculation and it is very hard at my experience level to notice a mistake. ALLSELECTED just feels too dangerous when you have many measures in SUMMARIZE.

[littlemojopuppy · Accepted solution]

@Anonymous 

@CNENFRNL is correct in that if you create a table using DAX it will not respond to filters, etc.  It will be created and populated only when the report file is opened or when data is refreshed.  I am not a fan of creating individual tables for each and every visualization...and you seem to agree with that sentiment  🙂

A low end solution could be to put filters directly onto the visualizations themselves and you can also alter the interactions between other visualizations and the one in question so they do not cross filter it.  Might work for what you need and it's simple and easy.

A higher end (and probably better) solution would be to create table variables in the measures themselves to include/exclude only what you want in the calculation.  For example, if you wanted to create a measure that calculates Total Sales only for a range of dates in a slicer it could look something like this

Measure =
VAR	SelectedDates = VALUES('Calendar'[Date])
RETURN

CALCULATE(
	[Total Sales],
	SelectedDates
)

 Another example would be this...if you only wanted to calculate Total Sales for all products (regardless of filter context) with sales greater than $1M and profit less than $100k you could do that like this

Measure =
VAR	ProductSummary =
	SUMMARIZE(
		Products,
		ALL(Product[ID]),
		"TotalSales",
		[Total Sales],
		"TotalProfit"
		[Total Profit]
	)
RETURN

CALCULATE(
	[Total Sales],
	FILTER(
		ProductSummary,
		[TotalSales] >= 1000000 &&
		[TotalProfit] < 100000
	)
)

There are - of course - many different ways to achieve both these results without necessarily using a table variable.  I'm just trying to demonstrate using table variables as filters in defining measures.  The good thing about including your filters in the measure definition is that it will override (if defined correctly) the filter context from slicers, cross filtering, etc.

Does that answer your question or did I make this more confusing?  🙂

[Anonymous]

Thx! I did not know you could use var inside measure expressions. That will help indeed.

[littlemojopuppy]

Glad I could help!  🙂

[CNENFRNL]

@Anonymous , any calculated table (created by "new table") can't interact with users at all.

[littlemojopuppy]

Yes, there are several ways.  But without any additional information, it would be very difficult to describe how exactly.

Are you looking it to be permanent, usable in visualizations and so forth?  Or just temporary as part of calculating a measure?