ALLEXCEPT allow filter not explicitly add in the ALLEXCEPT

[vbourbeau]

Hi,

Please help I'm on it since few day.

I don't understand I put a DAX request with ALLEXCEPT function and when I add a date column (yeah other column work) on the exception all other filter start to work. But I don't want it,  I only want the date column allowed to filtering.

Here my DAX: 

measure = calculate(SUM('Production'[hours]),ALLEXCEPT('Production','Production'[Date Production]))

When I click on segment on my report, exemple segment Production[Shift], the graph are filtered but I never add Production[Shift] in the ALLEXCEPT function.

Also when I filter with Production[Shift] the result is'nt right. I don't know why but the result is'nt the same that if I remove entirely the ALLEXCEPT function.

Thanks

[vbourbeau]

Hi,

I can't use the interactions because I want to use this mesure in a calculation as denominator. 

Look the above pbix it's a simple one and the behavior occur.

Play with each filter and see the measure change even if I use Allexcept. Only the date must affect .

https://we.tl/t-m4REX9QCnw

I don't find where I can attatch a file to the post? I put a wetransfer link but it will not stay long there...

[v-lili6-msft · Accepted solution]

hi, @vbourbeau

You could try this way as below:

step1:

Create a date table and then create a relationship with basic data table.

use ALL() Function in your formula like this

Measure = CALCULATE(SUM(data[Hrs]),ALL(data[Client],data[Quart],data[Quart day]))

Add the fields that will be dragged into slicer into ALL() conditional.

Result:

Best Regards,

Lin

[vbourbeau]

It's working but I don't understand why we need to create an other table?

[v-lili6-msft]

hi, @vbourbeau

This relates to the row context and filter context in the calculation.

In your measure, you add Production[Date Production] in the ALLEXCEPT function,

now the result only can be affected by Production[Date Production].

When I click on segment on my report, exemple segment Production[Shift], the graph are filtered but I never add Production[Shift] in the ALLEXCEPT function.

I think you should drag the field Production[Date Production] into the visual, when you select Production[Shift], Production[Date Production] is also be filtered, so result is affected.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Best Regards,

Lin

[vbourbeau]

Hi,

In my visual I already have both, Production[Date Production] and Production[Shift]. In the visual I remove the interaction between this 2 field but the Production[Shift] continues to afect the result even if is'nt on the ALLEXCEPT and without interaction with Production[Date Production].

[v-lili6-msft]

hi, @vbourbeau

 In the visual I remove the interaction between this 2 field 

What is this meaning? could you explain it to me in combination with the screenshots?

If you drag Production[Date Production] into visual with measure and other fields that could be affected Production[Date Production], then the result will be affected too.

So you could share a simple sample pbix or some simple sample data and expected output. Then we could help you get your expected result, Do mask sensitive data before uploading.

Best Regards,

Lin

[vbourbeau]

Here the measure with only date filter.

Here when I choose on Quart filter the measure change value? Even if I remove the filter intercation from DateProduction.

 

Any way my main goal is to have the measure afect only by the date. Because I'll use this measure as a denominator in a average formula. 

[v-lili6-msft]

 hi, @vbourbeau

There is the same thread like your case,

https://community.powerbi.com/t5/Desktop/Make-measure-ignore-specific-filter/td-p/336870/page/2

In this case, @Greg_Deckler said:

OK, I believe that this is an issue that @marcorusso and I took a look at in another thread and Marco wrote up a nice blog article about. I have the fix in the attached PBIX. Basically, take ShippingDate and create another table using DISTINCT('Table'[ShippingDate]) Then relate the tables. Use this other table as your slicer and change your measure to refer to this new table. As Marco explains, this is not technically a bug but a pre-filtering "feature" of DAX.

and you also could try this way:

Enable Edit interactions,

select Production[Shift] slicer, then select the no impact icon  for visuals which the measure is in.

For example

Best Regards,

Lin