Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

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

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


Helper I
Helper I

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






Super User
Super User

Hi @Allegro 

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors