select a single value from a many to many relation base on a field in the fact table

[Allegro]

hi everyone!

so i need to create a field for table in a report, this field comes from a table name station, my problem is that the data comes from a many to many relation and depending on a value from my fact table could be one or another station, along other conditions.

to give you an idea here is the model, its one fact table (Data_Workitems), two dimension tables (station & Pstn number) and a bridge table (stationpstnnumber) meaning one pstn number could be assign to many stations and a station could be assign to many pstn numbers

'stationpstnnumber'[stationid]   * -<-1  'station'[id]

'stationpstnnumber'[pstnnumberid]   * <--> 1 'pstnnumber'[id]

'DATA_Workitems'[toAddress] * -<- 1 'pstnnumber'[name]

in the Data_Workitems table the pstn number is know as the field name ToAddress, its the phone number to which clients dial to, so from there we have the ToAddress which could be assign to many stations, now to get the right station of the many that could be asign to the pstn number there is also another field on the fact table Data_Workitems called fromAddress which is the phone number from where the client is calling, the first three digits of this number is called the area code, now the station table has also a field called areaCode that contain muliple 3 digit numbers separated by a space like this:

so from the many stations assign to the pstnnumber (or the ToAddress on the fact table) we are going to return the one that contains the 3 digits from the FromAddress field of the fact table, so depending on the fromAddress the station name could vary,

if non of the 'station'[areacode] contains the first 3 digits of the FromAddress a blank() is return, if more than one 'station'[areacode] assin to the ToAddress field contains the first three digits of the FromAddress return the one with the highest 'station'[modifiedAT]

if you know the solution to my problem or you reccomen some tips would be much appreciated, i have been struggling with this for a while

thanks

[Allegro]

Hello @tamerj1 

its a simple table a bit long, I use a Date field, the toAddress field mentioned before and the rest of the table are measures calculating some attributes of the phonecall made, it looks like this

[tamerj1]

Hi @Allegro 

how does your table visual look like? What columns are you using to slices by in the this table?