Artificially assigning data to two different categories

[HM615]

I work at a university and we have a series of very dense, very complicated reports tracking applications/offers for study/registrations etc across multiple years/departments, split in myriad ways on lots of different tabs. I have one query that pulls in the organisation structure (faculty, department, course, etc). I have another query pulling in this year's application data, another pulling in last year's, another pulling in basic registration details across five years....and some more bits and pieces, but for illustration: it's an organisation table, then several other queries with the actual data. 

There is a new course coming that is going to be split between two faculties - it will be taught by staff in two different departments within two different faculties. They want to see that data for that course (which in my source data will  have to sit within one of them) to somehow be split, so the report allocates half the numbers of applications/offers/accpets/etc to each department. 

I don't even know where to start with doing this! There is already quite a lot of DAX coding that artificially changes departments (so things like foundation courses appear as their own 'department', although they officially belong to one of the academic departments). But thse applications are tied to a distinct course code, so all I'm doing there is looking for the course code and telling it to put it in "[Department] Foundation" on that basis. I don't know where to begin with trying to split actual counts of applications between two different departments.... Does anyone have any suggestions for approaching this?

[HM615 · Accepted solution]

Hello, 

I have found a way to make it work, although slightly different to the above suggestions. I think they would likely have worked if I wasn't attempting to do this within a very dense and complex report that already has a lot of linking on primary keys and then multiple variables to deal with various exceptions when it comes to categorising things into departments/colleges. I kept coming up against problems when I tried the above, but they might have been resolved with either a simpler report or if I could have started building from scratch. 

What I ended up doing was adding a new query which brought the data for this one course in a second time, but gave it a dummy course code and put it into a different department at the query level, then appended this to my original query. I then used the weighting column that was suggested to adjust any and all measures counting apps/offers/etc so that this course (which was now appearing twice, in the two different departments), was only counted as half. This seems to have worked! 

I appreciate everyone's suggestions - it was definitely helpful in getting to a solution, although I ended up doing things slightly differently. 

[v-dineshya]

Hi @HM615 ,

Thank you for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here.  Please do let us know if you have any further queries. 

Regards,

Dinesh

[v-dineshya]

Hi @HM615 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. In organisation Table, add a column AllocationFactor. Assign default value as "1" for all courses. For the special course add two rows, one for each department, each with 0.5 as the weight.

2. Create a Bridge Table, this table maps the course to multiple departments with associated weights.

3. Instead of a direct relationship from data tables to the organisation table, use the bridge table. This allows the same course to be associated with multiple departments.

4. Multiply counts by the AllocationFactor in your measures. Please refer below sample DAX code.

ApplicationsWeighted =
SUMX(
RELATEDTABLE('BridgeTable'),
'Applications'[ApplicationCount] * 'BridgeTable'[AllocationFactor]
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[HM615]

The problem is that the organisation table does not contain courses. It's a just the organisational strucutre - departments, schools, and faculties. Course data is then linked to this table via a primary key, and I can't have more than one primary key for the same course - so I can't put it in two places at once. 

I'm not sure if it's possible to do something in either DAX or in Power Query to basically tell it "for this course, put the data for this other course code" - which would basically double up the data for the course, but from there I could tell it to weight that course (or those two courses) as 0.5. 

I can't see a way to do it when my tables are linked on primary keys. Unless I re-write the entire report, which isn't really feasible in the time that I have.... 

[v-dineshya]

Hi @HM615 ,

Please follow below steps.

1. Create a Bridge Table between courses and departments, with an allocation factor. The Bridge Table contains the CourseCode, Department and AllocationFactor columns.

Note: You only need to include courses that require splitting or special handling. All others can default to 1.

2. In Power Query, duplicate the rows for the special course so that it appears twice once for each department. Then merge this with the bridge table to apply the allocation factor.

In Power Query, Filter the special course. Duplicate the row. Assign department manually or via merge with bridge table. Add allocation factor column. Append back to the main data table. You are not changing the primary key relationships . You are creating a new version of the data that’s already split.

3. Update your measure to multiply by the allocation factor.

ApplicationsWeighted =
SUMX(
'Applications',
'Applications'[ApplicationCount] * RELATED('BridgeTable'[AllocationFactor])
)

Note: If you are using a star schema, you might need to use TREATAS or LOOKUPVALUE depending on your relationships are set up.

4. If duplicating rows in Power Query is too complex or slow, Keep the original data as it is. Create a calculated table in DAX that duplicates the course data and applies the allocation factor. Use this calculated table in visuals/measures instead of the original.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @HM615 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[HM615]

Actually, this doesn't work. I can't have two different FK values for the same course in order to map one course to two different departments. 

[HM615]

Thank you for this suggestion! I will see if I can get it to work this way. I already have a very complex course > department > school > faculty mapping table - that's what my organisation table does. Would I need to replicate the whole thing - and have weightings for everything else as well as this one course for that to work? I'm thinking actually I just add a column to my existing table and everything gets 1 except this course.... I think we're onto something here! Thank you!

[Shahid12523]

Create a course-to-department mapping table with weights (e.g., CourseX → Faculty A 0.5, Faculty B 0.5). Join it to your data and use weighted sums in measures. This way the course splits correctly between both faculties.