cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
t055169b
Frequent Visitor

ALLEXCEPT function not working properly

Hello everyone, hope someone can help me on this.

 

I'm adding an ALLEXCEPT function to calculate a percentage of a column. It does what I want except for the Date dimension. 

t055169b_0-1664378776148.png

 

Relationships are those. MAP Calendar Master contains all days, MAP Calendar and TB only contains 1 day each month.

t055169b_1-1664378991695.png

 

I'm really struggling to figure out whats happening but even trying to use random columns from all the 3 tables related or even disabling MAP Calendar Master relationship it does not work...

 

Thank you a lot!¡

 

 

1 ACCEPTED SOLUTION

Even putting it as you said I couldn't achieve what I want. But I found another way to do it. I used the ALLSELECTED() function like this: 

 
CALCULATE([NSB ACT], ALLSELECTED('TB_AFO_ACT+AOP'[visual dimension1],'TB_AFO_ACT+AOP'[visual dimension2],'TB_AFO_ACT+AOP'[visual dimension3],'TB_AFO_ACT+AOP'[visual dimension4]))
 
I just selected all the dimensions in table visual rows and it worked. It was always getting the correct total.
t055169b_0-1664439555486.png

 

The only limitation is that you must use only a single table to filter, in this case TB AFO ACT+AOP. You can use the RELATED() for this issue. 
 
Thank you anyway!¡

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @t055169b ;

Because there is no date column in your picture, the grouping of measures is dynamic, there is no date column, how to group for date columns?
I'll give a simple example of the comparison between putting a date in the matrix versus not putting a date.

vyalanwumsft_0-1664437388814.png

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community


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

Even putting it as you said I couldn't achieve what I want. But I found another way to do it. I used the ALLSELECTED() function like this: 

 
CALCULATE([NSB ACT], ALLSELECTED('TB_AFO_ACT+AOP'[visual dimension1],'TB_AFO_ACT+AOP'[visual dimension2],'TB_AFO_ACT+AOP'[visual dimension3],'TB_AFO_ACT+AOP'[visual dimension4]))
 
I just selected all the dimensions in table visual rows and it worked. It was always getting the correct total.
t055169b_0-1664439555486.png

 

The only limitation is that you must use only a single table to filter, in this case TB AFO ACT+AOP. You can use the RELATED() for this issue. 
 
Thank you anyway!¡

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.