Different types of many-to-many relationships in Power BI
The sentence "many-to-many relationships" has different meanings in Power BI. The "classic" many-to-many relationships in a data warehouse is a design pattern requiring a bridge table. A many-to-many cardinality relationship in Power BI is different from the one commonly used in dimensional modeling, and it commonly solves a granularity issue in managing data coming from different data sources. This session clarifies design patterns and best practices for using the different types of many-to-many relationships in a Tabular model for Power BI and Analysis Services. About Speaker: Marco is a business intelligence consultant and mentor. He wrote several books about Power BI, Analysis Service, and Power Pivot. He also regularly write articles and white papers that are available on sqlbi.com. Marco is a Microsoft MVP and an SSAS Maestro, the highest level of certification on Microsoft Analysis Services. 𝙋𝙚𝙧𝙨𝙞𝙖𝙣 𝙋𝘽𝙄𝙐𝙂- 𝙈𝙞𝙘𝙧𝙤𝙨𝙤𝙛𝙩 𝘾𝙤𝙢𝙢𝙪𝙣𝙞𝙩𝙮: https://bit.ly/3IAg7xT 𝙇𝙞𝙣𝙠𝙚𝙙𝙄𝙣: https://bit.ly/32tGkif 𝙏𝙚𝙡𝙚𝙜𝙧𝙖𝙢 𝘾𝙝𝙖𝙣𝙣𝙚𝙡: https://t.me/PersianPBIUG 𝙔𝙤𝙪𝙏𝙪𝙗𝙚: https://bit.ly/3hk20RL Language: English
DAX Calculation find max value until certain date per employee (many-to-many)
Hello guys, i have a problem that is driving me nuts and I am coming to you for help. I have two tables: The first one details the working schedules per employee. It shows the schedule and the date it was submitted. The second one is an order table and it contains working orders, a date and the employee. I would like to get two new columns in the second table specifying the employee schedule. I specify columns as I need to do further calculations and I need to visualize the date without the employee field displayed. Even though any idea is welcome. Notice that both tables can have multiple times the same employee and the dates are not comparable! Example: Any recommendations? Thank you so much
How to multiply percentage from dimension table with amount from fact table - many to many relation
Hi, I have a problem getting the appropriate data from my DAX formula. Here's my model: fact dimBank dimSecurites Fact table is a day to day table with debt amortization plan ordered by tranche (%dimSecurities) dimSecurities is a table with all specific data for the vessel, each having unique key %dimSecurities dimBank is a table where the split between multiple banks, where a %dimSeucrities sometimes occur 3-4 times depending on how many banks involved This is a simplified version of my data model: Looking at this table, the data is correct when I add other dimensions, like "Share" or even better "%dimSecurities", but the total sum is incorrect as it is a result of the average share multiplied by total debt... However, I would like to show these data graphical in a barchart, but then I cannot add the aggregation, hence the total sum is being used, wich is incorrect, 12,017,545 when the correct sum should be more like 7,961..... etc This is my DAX formula... debt by lenders = CALCULATE(SUMX('factSecurity',factSecurity[Loan Balance Start] * LOOKUPVALUE(dimT16_Valutakurs[Currency Rate], dimT16_Valutakurs[Dates], Max('Calendar'[Date]), dimT16_Valutakurs[FRA_VAL_KODE], factSecurity[Currency])), FILTER(factSecurity, factSecurity[Dates] = Max('Calendar'[Date])))* AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share]) Any help is much appreciated 🙂 Thanks E
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
Problem many to many building dates
Hi Everyone, I appreciate with you guys can help me with this DAX, I need to build a date (Ex : 20220213), using 2 tables. At column dax_YearMonthNo I need this date. Using this DAX Formula i could get YYYYMM (Image1) when i try to make a lookup to bring the earliest date from Calendar it fails, (already tried using Earlier) Image 1 Image 2 Calendar Table 1Many-to-Many Relationships for RLS in Power BI Desktop for Report Server / On-Premises
Dear Power BI Community, I have a data model in which each user (principal-username) may have several sub_ids (e.g. business units). The CEO for example has access to all business units, hence, his principal-username matches several sub_ids. In the fact sales table each transaction has a sub_id. Now I aim that when a user logs in, Power BI recognizes his principal username and he sees only the transactions that are in his sub_id. However, since Power BI Desktop for Report Server seems not to allow many-to-many relationships, I have absolutly no clue how to implement RLS. I appreaciate any kind of your help. Cheers Rob
