Correct measure to use to mapping in Bridge table
Hi, Being a new bie to PBI Need help to arrive at correct measure to take in to account the bridging table . Recreated my actual case with simple sample data. Two fact tables,Three dim tables and a Bridge table. Bridge table has n to 1 relationship as well as 1 to n relation ship between ID1 and ID2 My current measure does not take in to account the relation ship while calculation as it still calculates the values for independent IDs and not taking the ID mapping to account. Expected correct values given in red after aggregation. https://drive.google.com/file/d/1NwijYKBDzng82mm6jXpthB8SkWAG1OJo/view?usp=drive_link Enclosing the PBIX sample file. Deeply appreciate your support. Thanks.
A fact table getting info from dimension table and fact table then grouping & distinct count
Hi , My scenario is 2 fact tables (point & trans) connecting to a dimension table (profile) point table is filtered by calendar table (e.g. 3-Aug-2023 to 4-Aug-2023), there are 124 members with this date selection a. getting the regist date of these 124 members from profile table b. getting the last order date from trans for these 124 members from trans table 1. when regist date of member >= 3-Aug-2023, would call this member group as Group A and get the distinct count of the members 2. when regist date of member < 3-Aug-2023 and last order date >= 3-Aug-2022, would call this membes group as Group B and get the distinct count of the members 3. when regist date of member < 3-Aug-2023 and last order date < 3-Aug-2022, would call this member group as Group C and get the distinct count of the members 4. when regist date of member < 3-Aug-2023 and last order date is null, would call this member group as Group D and get the distinct count of the members have written 2 measures [_Last_Order_Date], [_Last Order Date (for points member)] about this, but not sure how to proceed for [_Historical Purchase Group Count] have uploaded pbix file to onedrive , could anybody help to get this work? fact-bridge-fact.pbix
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 thanks
