Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm struggling with a problem with filters and I hope someone can help.
I have a measure like this:
Solved! Go to 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
Hi @H3nning ,
Could you please provide some sample data of tables(DMBI_VfAktivitäten, DMBI_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
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
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:
So these two tables are linked by _mydate and Beginn, which are both dates.
I have a matrix like this:
And i have a hierarchy slicer like this:
And it is configuered like this:
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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |