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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
vbourbeau
Resolver II
Resolver II

ALLEXCEPT allow filter not explicitly add in the ALLEXCEPT

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
1 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.

7.JPG

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:

8.JPG9.JPG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
vbourbeau
Resolver II
Resolver II

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...

 

hi, @vbourbeau

You could try this way as below:

step1:

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

7.JPG

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:

8.JPG9.JPG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

v-lili6-msft
Community Support
Community Support

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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].

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Here the measure with only date filter.

2018-12-13 08_20_56-Window.jpg

 

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

2018-12-13 08_22_41-Window.jpg 

 

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. 

 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 no impact icon for visuals which the measure is in.

For example

1.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.