Connecting 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.
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, Willprevious week calculation repeating over empty dates weekly to daily relationship
I have the below data model. Where the fact table is weekly and calendar is daily. When I calculate the previous week value it adds all the dates from the calendar rathen than keeping only dates in the fact table. PREVIOUSWEEK Value WDT = VAR _Today = SELECTEDVALUE(Lookup_Calendar[Date]) VAR _WOT = WEEKNUM( _Today, 1 ) -- Number 2 determines that the week begins on Monday. VAR _LW = _WOT - 1 RETURN CALCULATE( SUM( Data[Units]), FILTER( ALLSELECTED( Lookup_Calendar ), Lookup_Calendar[Week Of Year] = _LW && Lookup_Calendar[Year] = YEAR(_Today ) ) ) pbixfileBilling and Collection Report Problems
Hi I am having trouble creating a table with the following fields: screenshot is an excel demo: Date Bill Number Amount Billed Amount Collected 01/01/2019 100000 50000 0 02/02/2019 100000 0 20000 01/03/2019 200000 100000 0 01/01/2020 100000 0 30000 01/01/2020 200000 0 100000 My data is hosted on an SQL server that's getting refreshed via API. So I direct query that data into powerbi and publish my reports on the service. The way the data is now is the following: 1) Bills Table: Bill_ID, Bills_Issue_Date, Net Billed 2) Allocations Table (Payments): Bill_ID, Allocation_Date, Net Collected I tried creating a calendar table and linking both above tables to it based on issue date and payment date in addition to that i tried linking both tables to each other by Bill_ID But it's not working. Can someone help me fix this relationship so I can make it work please? I can't link the dates table to only one date because I need the bill_issue date in addition to the date of any payments. Also One bill can have multiple payments or 0 payments (still awaiting payment). I want a result as follows:
