cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H3nning
Helper II
Helper II

Allexept and hierarchy slicer

Hi,

 

I'm struggling with a problem with filters and I hope someone can help.

 

I have a measure like this:

Calculate
( SUM(
'DMBI_VfAktivitäten'[_78er]
)
;
ALLEXCEPT(
'DMBI_VfAktivitäten'
;DMBI_Category[Category]
;'DMBI_VdTimetable'[last365];'DMBI_VdTimetable'[myDate];'DMBI_VdTimetable'[prior365];DMBI_VdTimetable[_myDate];DMBI_VdTimetable[_TimePeriod]
;DMBI_VdTimetable[Day];DMBI_VdTimetable[Month];DMBI_VdTimetable[Year]
)
)
 
This measure is used in a matrix and populates one column of values seperate by category. Please don't ask why i need it like this, believe me I need allexept.
There is also a slicer. I want to use a hierachy slicer from the market place, to filter by time. Good thing is, that this hierachy slicer can be expanded like a hierarchy. I used DMBI_VdTimetable[_myDate] as Field and because it is a date PowerBi builds a hierarchy automatically. 
Bad thing is, that it is not working. If I use a normal slicer with _mydate it works. But with the hierarchy slicer there is just no effect. It seems, that the allexept somehow is the problem. Measures without allexept work fine with the slicer. How can i make it handle hierarchies?
 
Thanks in advance!
1 ACCEPTED SOLUTION

The solution to the problem is indeed the reference of the hierarchy in the allexept statement. It works when I add

;'DMBI_VdTimetable'[_myDate].[Day];'DMBI_VdTimetable'[_myDate].[Month];'DMBI_VdTimetable'[_myDate].[Quarter];'DMBI_VdTimetable'[_myDate].[Year]

 

So as I assumed it is not sufficient to refer to the variable [_myDate] only. PowerBi creates a hidden table with the date hierarchy in the background, which of course was not mentioned in the allexept statement. By adding the code above, this reference is added.

 

Best regards

Henning

View solution in original post

5 REPLIES 5
yingyinr
Community Support
Community Support

Hi @H3nning ,

Could you please provide some sample data of tables(DMBI_VfAktivitätenDMBI_Category and DMBI_VdTimetable) which be involved in your measure and your expected results in order to make troubleshooting? Please explain more details with some examples and provide the related screen shots if it is necessary. It is better if you can provide a sample pbix file. Thank you. 

Best Regards

Rena

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

Hi @H3nning ,

 

There is an existing thread on a similar issue on how you can use a slicer with an measure that uses AllExcept.

https://community.powerbi.com/t5/Desktop/Slicer-and-ALLEXEPT/m-p/670971

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi @Pragati11 , 

 

In my opinion I built it just as described in the link you provided. But I still have that problem. So what did I do wrong?

Hi @yingyinr ,

 

I don't see where I could upload anything. How is that done here? But i would still have the problem, that I'm not allowed to share the data. So i would have to make samples anyway.

 

But i will try to explain with screens:

 

I thin the dependencies can be reduced to this:

H3nning_0-1598444773818.png

So these two tables are linked by _mydate and Beginn, which are both dates.

 

I have a matrix like this:

H3nning_1-1598444892688.png

And i have a hierarchy slicer like this:

H3nning_2-1598444935330.png

And it is configuered like this:

H3nning_3-1598444973831.png

The measure that resolves in 373 in the matrix is explained in my first post. Problem is, taht the slicer is not filtering the matrix at all.

 

In addition, this works perfectly:

H3nning_0-1598445887479.png

Here I used the standard slicer, which has no date hierarchy in it. I guess i have to relate to the hierarchy in the allexept command somehow... 

 

Best regards

Henning

 

 

The solution to the problem is indeed the reference of the hierarchy in the allexept statement. It works when I add

;'DMBI_VdTimetable'[_myDate].[Day];'DMBI_VdTimetable'[_myDate].[Month];'DMBI_VdTimetable'[_myDate].[Quarter];'DMBI_VdTimetable'[_myDate].[Year]

 

So as I assumed it is not sufficient to refer to the variable [_myDate] only. PowerBi creates a hidden table with the date hierarchy in the background, which of course was not mentioned in the allexept statement. By adding the code above, this reference is added.

 

Best regards

Henning

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!