Forum Discussion
ALLEXCEPT Function on matrix visual using fields from related table.
Hi there. Relative newbie to PowerBI and first time forum poster, those have been using Tableau for years. I hope I'm asking in the correct forum.
I have a question about replicating Tableau FIXED LOD in PowerBI using the ALLEXCEPT function and displaying data in a MATRIX visual - and why it seems to break if you include a field from a related table.
Here's the tables and relationship in my model as an image.
There is a many to 1 relationship between enrolments and regions based on postcode.
I now want to create a matrix visual in powerbi that will show share of total % enrolments by student citizenship and year (so basically a market share measure that is always calcualting with respect to student citizenship and year) - no matter what fields are in the matrix visual.
I've created one measure to get total enrolments:
Total Enrolments =
Sum('Enrolments'[Enrolments])
I've then created a second measure to basically
Total Enrolments by Citizenship and Year=
CALCULATE (
[Total ENROLMENTS],
ALLEXCEPT (
'Enrolments',
'Enrolments'[Student Citizenship],
'Enrolments'[Year]
)
)
And a third measure for market share:
What I've found is that the second and third measures works fine when every field is used in the same table: see image below (yellow highlight indicating working market share):
However, as soon as I bring in the field regions from related table regions, it measure 2 seems to break ....and I cannot figure out what is wrong.
For some reason the ALL EXCEPT seems to break when a field from a related table is brought into the visual.
I'm aware that I could merge the tables in the backend model - but this is not an option for me currently. I basically need this to work just using DAX.
I hope I've explained that well enough and hoping that someone might point me in the right direction as to why my ALL EXCEPT function doesn't seem to work (assumign that is the root cause).
Many thanks in advance.
Anonymous
The issue you are going to struggle with by having that structure is that DAX is designed to filter row values, so having the fields in columns makes it easier/more efficient. I would suggest a structure for your data resembling something along the lines of:
6 Replies
Anonymous
Just to confirm...Does the depiction of your tables reflect the actual table structure? In other words, are the fields in rows?
(A tabluar model is based on having fields as columns)
Anonymous
The issue you are going to struggle with by having that structure is that DAX is designed to filter row values, so having the fields in columns makes it easier/more efficient. I would suggest a structure for your data resembling something along the lines of:
Hi Paul. I'm really sorry - but I'm not following you and apologise if I've accidentally been misleading. The fields are columns in my data model.
It might help if I post screen shots of the sample data I've been using.
Enrolments:
Regions
Here's the model relationships. The relationship is many: one based on postcode.
Hi Anonymous ,
From your model view, it shows that table Regions can succesfully filter the table Enrolments,but it doesnt hold true vice-versa,so you'd better modify the direction from single to both to check whether it works.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
