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
laurent_rio
Helper I
Helper I

How to remove category in column with DAX-allexcept not work

Hi,

 

I want to calculate group average training duration that count total per group not per position. 

Capture.JPG

I try use ALLEXCEPT Dax but not work 

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALLEXCEPT('Job Grade','Job Grade'[Job Grade]))

Jobgrade is my table name that have list of position , where i calculate average duration/staff use measures

Please help
1 ACCEPTED SOLUTION

@laurent_rio , if you want other filters to work

 

Group Average Duration/Staff = CALCULATE([Duration],filter(allselected(Course_Completion_Listing),Course_Completion_Listing[BU] =max(Course_Completion_Listing[BU]) ))

View solution in original post

5 REPLIES 5
laurent_rio
Helper I
Helper I

Yes it works !!! Super Thanks

laurent_rio
Helper I
Helper I

Hi it seems my DAX not work if i have filter. My filter is Business unit , 
using the formula 

Group Average Duration/Staff = CALCULATE([Duration],ALLEXCEPT(Course_Completion_Listing,Course_Completion_Listing[BU]))

will lock all the business unit to show eventhough filter applied 

Any idea ? 

@laurent_rio , if you want other filters to work

 

Group Average Duration/Staff = CALCULATE([Duration],filter(allselected(Course_Completion_Listing),Course_Completion_Listing[BU] =max(Course_Completion_Listing[BU]) ))

Hi @amitchandak 

Now the questions if the filter have all selection, it seems this DAX
Group Average Duration/Staff = CALCULATE([Duration],filter(allselected(Course_Completion_Listing),Course_Completion_Listing[BU] =max(Course_Completion_Listing[BU]) ))

Not give expected result

Here for example if i choose 1 BU, the result is OK 
1.JPG
But if i choose all BU or multiple BU Selection, i expect the group will show same total average hour/staff 
2.JPG
Can you help to modify the DAX so it work for all or multiple BU Selection ?

amitchandak
Super User
Super User

@laurent_rio , Allexpect will allow that filter to pass, means job Grade filter iwll pass

 

Try like

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALL('Job Grade'[Job Grade]))

 

or

 

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALLselected ('Job Grade'[Job Grade]))

 

 

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],removefilters('Job Grade'[Job Grade]))

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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