top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Writer's pictureBrent Jones

How to add a specific value to ALLSELECTED with DAX?

Problem:

Suppose you want to create a measure that utilizes ALLSELECTED, but you also always want to include a specific value in the filter context that may exist outside the values available within ALLSELECTED (i.e. the user deselected the value from a slicer or filter).


Your business requirement is to compute the amount of sales for all the selected brands, but also include the brand "Fabrikam" in the calculation (if "Fabrikam" is selected, ensure that it is not double counted in the total).



Solution:

How to add a specific value to ALLSELECTED ?

In this case, we can use the UNION function to help us. The idea is to union two tables together to use as our CALCULATE filter; one for the ALLSELECTED, and one for the specific brand value of "Fabrikam".


UNION(	
	ALLSELECTED( Products[BrandName] ),	
	TREATAS( {"Fabrikam"}, Products[BrandName] )
)

Putting it in our CALCULATE, we get the following:

All Selected Brands (including Fabrikam) = 
CALCULATE(
	[Total Sales],
	UNION(
		ALLSELECTED( Products[BrandName] ),
		TREATAS( {"Fabrikam"}, Products[BrandName] )
	)
)

This code is useful in scenarios where you want to include specific values in your calculations, irrespective of the user's selections, while still taking into account the other selected values dynamically.





NOTE: We could have relpaced the TREATAS function with the following 'IN' method:

Products[BrandName] IN {"Fabrikam"}

Indeed, it produces the same results and looks slightly cleaner (readability). TREATAS, however, is more optimized and is the recommended way of building the query.

Comments


  • Facebook
  • Twitter
  • Instagram
bottom of page