CODEWITHSUNDEEP
powerbidaxazure-analysis-services
Seymour
Seymour

Reputation: 3264

Power BI DAX: ALLSELECTED with exception of one column

Context

Because of some business requirements I found myself with a DAX Measure calculated on a Fact and using an ALLSELECTED over the entire Dimension. The dimension contains the following fields 'Dimension'[Field 1], 'Dimension'[Field 2], 'Dimension'[Field 3], 'Dimension'[Field 4].

[My Measure] := CALCULATE(SUM(Fact[Quantity]), ALLSELECTED('Dimension'))

Question

Now, for some other specific business requirements, I need ALLSELECTED to work on all fields of 'Dimension' except for 'Dimension'[Field 4].

My current implementation becomes as follow:

[My Measure] := CALCULATE(SUM(Fact[Quantity]), ALLSELECTED('Dimension'[Field 1], 'Dimension'[Field 2], 'Dimension'[Field 3]))

This does the job but is not a long-term solution. Is there a more elegant and robust way to state the same DAX query, something like ALLSELECTED('Dimension, EXCEPT('Dimension'[Field 4]))

Upvotes: 1

Views: 9158

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

[My Measure] :=
CALCULATE(SUM(Fact[Quantity]), ALLSELECTED('Dimension'), VALUES('Dimension'[Field 4]))

VALUES('Dimension'[Field 4]) will be evaluated in the original filter context, and thus contains filtered values of 'Dimension'[Field 4]. This can be used to apply the filter again which has been removed by ALLSELECTED.

Upvotes: 4

Related Questions