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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
H3nning
Resolver I
Resolver I

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
v-yiruan-msft
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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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 @v-yiruan-msft ,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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