Best way to link multiple columns and linked fact tables back to one dim table to retrieve a value

[locka]

I've read and looked a lots of articles but I'm still not sure the best way to go about this.

I have a number of fact tables.  One contains information about the ticket and other contains list assignments of that ticket.

Main fact table (Fact_Fatults) has a number of columns which refer to the id of the agent as well as it's team.    The other fact table(Fact_FaultsAssignmentHistory) has a list of agent id that the ticket has been assigned too.  Both fact tables are joined by faultid. 

What I'm trying to is pull through the agent's name using there agentid.(Dim_Agent)  For all the references such as: -

CreateAgentID,ResolvedAgentID in fact_faults
AssignedAgentID in Fact_FaultsAssignmentHistory

Problem is I'm not sure the best way to go about this. I understand I can only have 1 active relationship between the fact and dim tables  but cannot see how I link them in away i can pull the data based on particualr coulmn from fact tables.  So that they can be used in both tables\charts or slicers.   All the examples with use of userelationship seem to do caculations only.  While I could use lookupvalue, ive read that's not very efficient.  Neither is duplicating the dim_agent table, or creating caculated columns that pull anwser through

I was wondering what the best way to do this is.  Any help would be greatfully received?

[locka · Accepted solution]

Hi thanks for the feedback and for novice comment!  While I'm by no means an expert neither am I novice.  Sorry if my question comes across as such.  I do understand Star scheme and the data is being Transformed, though that is mostly being done at source in SQL via my colleague.  We are not using a flat table model or anything remotely like that.  Quite open to fact we may have a structure in correct.  

Apologies If I have haven’t provided complete structure as it contains a lot of data.  With around 45+ tables, just for the data from ITSM tool.   ITSM tool itself is complex enough database structure.  We do have some constraint’s due to their structure and how it processes and stores & updates the information.    

This is large dataset which is being used to report on a number of different aspects of our ITSM tool, so while not every report will use all of parts, they are all required.  Wherever possible I'm looking to reduce number of fields.  Not create calculated columns or duplicate tables.  Which was hence my question. 

I will look at the best way I can provide some further information, so that I might help people in guiding me further.

[speedramps]

Learn about star schema data modelling here:-

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

https://www.youtube.com/watch?v=vZndrBBPiQc

https://en.wikipedia.org/wiki/Star_schema

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimen...

It is important for Power BI novices (such as yourself) to understand the different between raw data and the semantic data model.  You are clearly imputiing the raw data "as is" with no ETL (Extract Translate Load) to "transform" the data form its raw state into star schema dimension tables, fact tables anad relationships.

Data modelling and ETL is an advance skills and takes a while to grasp.

Since you have not provides any table layouts or example data, we cant help you any futher.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confusion.

Rename columns to user friendly names.
Also provide the example desired output, with a clear step-by-step description of calculations the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gertting free expert help, so please put lots of proper effort to asking questions and providing examples. 😀