Help with custom multi-filter(?)

[artfulmunkeey]

Hi all,

I am having some trouble creating a custom filter and am starting to think I am over complicating the problem. Could some one please be kind enough to offer some advice?

I have the following categories for ActivityTypes (coming from timesheets):

• Support
• Non-Billable
• External
• CAPEX
• BD
• Sick
• Other

I wish to categorise them as UtilisationTypes as follows:

• Billable
• Productive
• Non-Productive

I can create a simple key with a header, however, the issue I have comes from the mapping and having duplicate values:

UtilisationType	ActivityType
Productive	Support Services (Corporate, BU, Division)
Productive	Non-billable project (Direct costs for consultants)
Productive	External Projects (FFP, LOE, FUP)
Productive	CAPEX Projects
Productive	Business Development
Non-Productive	Absence - Sickness
Non-Productive	Absence - Other
Billable	External Projects (FFP, LOE, FUP)
Billable	Business Development

Any advice would be welcome! Thanks, Alex

[Anonymous · Accepted solution]

hello

 U can create a fact table for utilization type and map it to the other table. You can refer below. See below how I solved it. I hope this is what you wanted. 

[artfulmunkeey]

Updated for clarity - 

I attempted to solve the problem through relationships, however, sometimes an activity type belongs in more than one Utilisation Type, for example; BD and External Projects need to appear in Productive and Billable categories (eventually I wish to use Utilisation Type to filter my data).

See below for my attempted logic

[PaulDBrown]

@artfulmunkeey 
How are you going to classify the rows in your fact table to cater for the duplicates? In other words, if your fact table has a row for "Business development", how will you know whether it's "Billable" or "Productive"? 
It seems to me you will need to create anew column in the fact table to establish each row's "Utilisation Type". You can then establish separate dimension tables for "Activity Type" and "Utilisation type".

[amitchandak]

@artfulmunkeey ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[artfulmunkeey]

Sorry for not being clear.

I attempted to solve the problem through relationships, however, sometimes an activity type belongs in more than one Utilisation Type, for example; BD and External Projects need to appear in Productive and Billable categories (eventually I wish to use Utilisation Type to filter my data).

I hope the below image is of further use.

Thanks in advance!