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: EnglishConnecting 2 different sums in Dim and Fct Table between Hierarchy
Hello Guys, I have Fct_Amortized_Cost table and Dim_EA_SavingsPlans. They are connected with 1 to many relationship like here: the issue is that i am trying to use hierarchy : MeterCategory--> MeterSubcategory from Fct table and show CommitmentAmount per day: ROUND(sp.Commitment_Amount * 24, 2) AS "Commitment_Amount_Per_Day", this is sql for it. CommitmentAmount is in Dim table. the issue is that i am not seeing proper values when building Matrix visual. I have build a dax: Commitment_Amount_Per_Day = ROUND(SUM(Dim_EA_SavingPlans[Commitment_Amount]) * 24, 2) but when i am doing matrix i am not seeing proper results. Core Issue: No filter context propagation Your matrix uses MeterCategory, MeterSubCategory, and potentially ResourceType — these fields: Come from other tables (not Dim_EA_SavingPlans) Are not directly related to Dim_EA_SavingPlans Therefore, do not filter Dim_EA_SavingPlans, even if they are part of a visual So SUM(Dim_EA_SavingPlans[Commitment_Amount]) just adds up everything from the entire table on every row — unless a DisplayName or ID from Dim_EA_SavingPlans is in the visual. this is explanation from chatgpt. But how to make this properly? Best, Jacek
Trouble using DAX to calculate properly between multiple active/inactive relationships
Hello, I'm trying to understand if leveraging the Power BI relationships is a good place to do this, or if it's better suited to something like Power Query, or if we just write a database query that gives the proper output. We've been trying to get away for having so many custom views in the database so I'm trying with in app relationships first. In my scenario, the data source is a relational database with several tables. We have unique IDs tied to individual users, and there is a People Table that holds all of the user information such as their Names. I have to retrieve the names for multiple fields, which means Power BI needs a mix of active and inactive relationships established. Screenshots of the relationships are at the bottom of the post. Another workaround we thought of that I don't like is having multiple/separate queries pull the ID and Name from the people table and we have an active relationship and a new query for every named individual I'm trying to surface. The main active relationship I started with was joining 'report er_log' [lead_project_manger_id] to 'report people' [id]. I'm using a simple IF statement that successfully returns the Lead Project Manager name when those two IDs match - success! Lead PM Name = IF('report er_log'[lead_project_manager_id]=RELATED('report people'[id]),RELATED('report people'[full_name])) The issue comes in for the inactive relationship between 'report activity' [record_owner_id] to 'report people' [id]. Any formula I've used that didn't throw a relationship error will ignore the inactive relationship entirely and just return the PM Name. e.g. my latest attempt was using LOOKUPVALUE. my understanding is that LOOKUPVALUE should ignore relationships but it definitely doesnt and just gives me PM names and not all record owners LOOKUPVALUE('report people'[display_name],'report people'[id],'report activity'[record_owner_id]) My first attempt was using a simple IF statement. this also just returns PM names and has issues because RELATED only likes the active relationships IF('report activity'[record_owner_id]=RELATED('report people'[id]),RELATED('report people'[full_name])) The only thing i found that might work for the USERLATIONSHIP function throws an error because in this context there's no active relationship with report people CALCULATE(RELATED('report people'[display name]),USERELATIONSHIP('report activity'[record_owner_id],'report people'[id])) Any ideas are appreciated. I'm starting to play with Power Query, and worst case I think my workaround will be fine... I just don't want to have so many distinct queries going to report people if we can help it. EDIT: I tried the workaround for funsies and it seems like the same relationship issue with the original people table is still causing heartache.
Relationships Break When Server Refreshes
Hello All, I'm reposting this as I did not get any feasable solutions and I'm pretty sure this topic went stale. Also, I'm using the May Desktop and Server edition of PBI. I've got this really strange issue that only rears it's head when viewing my dahsboard from the report server. A few of my tables have a dynamic X axis, where I can select how I want to view my axis from a slicer drop down. I've done this by adding an index column to my source data and creating a reference table from it. Next, I unpivoted the columns in the reference table that I want shown in my slicer drop down. I create a relationship between the two tables using the Index as the bridge and it's cross filtering in both directions (pic1). The slicer dropdown field has the ref table "attributes" and the chart axis field contains the "values" from the ref table. Boom, it works as expected in PBI Desktop. Great! Here is a pic showing what it looks like and using a page level filter to prove that the data is filtering correctly (pic 2 & 3). However, when I publish it to the server and it refreshes the relationships go haywire and I get funky data when filtered (pic 4) ie. When page level filter is for West, Visual should only show the West Divison, not all. (Just to clarify, the relationships look fine in the server after publishing until I give it a refresh.) The next thing I do is click "edit in PBI Desktop" and once it's on my desktop, what do you know, all the realtionships are fixed again and the report functions as intended without me doing anything. So why are the relationships getting skewed in the report server, but get fixed automaticaly once I pull the report into the Desktop app? I can asume something with the indexes is going off the rails during the refresh, but then why the automatic fix once pulling the report into the desktop app. Can anyone please provide me with some insight on this issue i'm having? Relationship set up: Finished Product Works in Desktop App Does not work when server refreshes
Dax formula that does not filter on calender table
I need to know how my DAX formula called "Fakturert" needs to be updated to filter on dimKalender. I have 3 tables Relationship between JobTask[Job No_] -->[JobLedgerEntry[Job No_] dimKalender[Date] -->[JobLedgerEntry[Posting Date] In Power Bi report view in my table where I use JobTask[Job No_] When I use dimKalender for filter it only filters correctly for one of my DAX formulas. Incorrectly for this Dax Fakturert = SUMX( FILTER( JobTask, JobTask[Job Task Type] = 4 ), CALCULATE( SUM(JobLedgerEntry[Line Amount (LCY)]), FILTER( ALL(JobLedgerEntry), JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) && JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) && JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) && JobLedgerEntry[Source Code] = "SALG" ) ) ) Correct for this DAX FakturertLinje = VAR SelectedJobNo = SELECTEDVALUE(JobTask[Job No_]) VAR SelectedJobTaskNo = SELECTEDVALUE(JobTask[Job Task No_]) RETURN CALCULATE( SUMX( FILTER( JobLedgerEntry, JobLedgerEntry[Job No_] = SelectedJobNo && JobLedgerEntry[Job Task No_] = SelectedJobTaskNo && JobLedgerEntry[Source Code] = "SALG" ), JobLedgerEntry[Line Amount (LCY)] ) ) Dataset Link to pbix file
Bypassing the RELATED() function for inactive relationships
I need help with part of my model. Part of my model looks like this (the identical column names are the ones related to each other): My problem is that there are Raw Data entries that don't have an Order ID, but are still important in the visuals, which get filtered out when I use "Line Names" as a Slicer filter in my visualization, beacuse of the current active relationships. I can solve this by switching the active relationship from Lines-to-Orders to Lines-to-Raw Data. Unfortunately, my calculated column in the Orders table relies on this current active relationship and looks like this: Max Order Speed (cases/min) = MIN( MIN( DIVIDE( VALUE(RELATED(Lines[Max Speed Filler (pcs/min)])), Orders[Products per Case], 0 ), VALUE(RELATED(Lines[Max Speed Packer/Palletizer (cases/min)])) ), DIVIDE( VALUE(RELATED(Lines[Max Speed MPU (t/h)])) * 1000, // convert t/h to kg/h RELATED(Products[Product Weigth]) * Orders[Products per Case] * 60, // convert to cases/min 0 ) ) When I change the active relationships, my RELATED() functions can't refer to the Lines table anymore. I'd like to change my DAX expression to bypass this function, so my calculated column stays active when I change the active relationships. I think that's easier than to keep the relationships as is and in some way managing to not filtering out the raw data that doesn't have an Order ID... I tried asking ChatGPT for help, but it just keeps trying adding in CALCULATE, USERELATIONSHIP, SUMX, FILTER (which requires me from switching from a calculated column to a measure), but without avail. Any recommendations?
Using DAX to hide entries in table if there are low counts
Hi all, I have a Power BI table constructed using data with complex relationships. It allows a comparison of counts of distinct user IDs based on two data sources, one of which has RLS based on the user logged in. The problem I am having is that when counts are low in the table (<5), I would like to be able to hide the values. If there is only one value hidden, I must also hide another value to prevent back-calculation of the first hidden value. The outcomes have three possible values (Y (Yes), N (No), and Not answered). Thus, if the count of Yes is <5 (and the others are both above 5) I want to hide the next biggest count as well (or any blank). The tables are broken down and filtered, and both these functionalities are controlled by slicers. Add in the RLS and it's all a bit complicated and I am not particularly great at DAX. I would ideally like the suppressed values (blanks, <5 or additionally hidden values) to be replaced by 'SV' (to indicate suppressed/hidden values). I have shared an example pbix file here: https://drive.google.com/drive/folders/1oHDGSgUSM6CxQMD3LauerE4G6lVVKWj8?usp=sharing Any suggestions would be gratefully received, Will
