Strange behavior in CALCULATE with USERELATIONSHIP and FILTER
I have a fact table with four main columns: “Type”, indicating whether the row is “Plan” or “Real” “Billed Date” “Ship Date” “Volume” The fact table is related to my dCalendar table through the "Billed Date" column. However, I need to create a visual that shows each day of the current month alongside the real shipped volume per day. The issue is that I’m getting two different results with my measures, and I cannot understand why. Here’s what I tried: First Attempt Volume = SUM(fact[volume]) 1ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date])) Then, I created a table visual, placed dCalendar[Date] in the rows, and added a slicer on the “Type” column, filtering it to “REAL”. ✅ Result: This method works as expected — I get the correct daily volumes. Second Attempt (Expected to be better): 2ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date]), fact[Type] = "REAL") However, when I use this measure in the same visual, I only get values for the days where the Ship Date and the Billed Date are the same. For all other days, the result is blank. Third Attempt (I don’t know why it works): 3ºTry = CALCULATE([Volume],USERELATIONSHIP(dCalendario[Date],fact[Ship Date]),fact[Type]="REAL") When I delete the FILTER DAX and use the “native” filter from CALCULATE, the measure works. Why??? My understanding and question: I expected that adding fact[Type] = "REAL" as a filter inside CALCULATE would behave the same as applying the slicer at the visual level — but it doesn't. Instead, it seems to further restrict the context and only shows rows where both the active relationship on Ship Date and the Type filter apply, but in a way I didn’t anticipate. Can anyone help me understand why this is happening? Is there a difference in how context transitions or row filters are applied in this scenario with USERELATIONSHIP + FILTER compared to using a slicer? Also, what would be the best practice for handling this kind of situation — should I keep relying on slicers for these filters, or is there a more robust way to incorporate the Type filter within the measure? Thanks in advance!
Help Adding Calculate Conditions to Switch Function
Need help adding correct calculations into my Switch function using two tables: 1) PPI (premium per phone interaction) table 2) PROD sales table Calculate "Total PPI" using this formula: Total Premium (from PROD Sales)/Total Count of PPI (from PPI) There is active relationship between "Date" and inactive between "Advisor" (not sure if I set it up correctly) I've included my PBI project with sample data and visuals to download/view: Power BI PPI Project Below my existing Switch statement; however I'm not getting correct results: Total PPI = SWITCH( TRUE(), //Case 1: When Advisor and Date selected HASONEFILTER('PROD SALES'[Advisor]) && HASONEFILTER('PROD SALES'[Date]), CALCULATE( DIVIDE([TotalPremium], [PPI Calls])), ), //Case 2: Only date selected HASONEVALUE('PROD SALES'[Date]), CALCULATE( DIVIDE ([TotalPremium],[PPI Calls]), USERELATIONSHIP('PROD SALES'[Date], 'PPI'[Date]) ), //Case 3: Only Advisor is selected HASONEVALUE('PROD SALES'[ADVISOR]), CALCULATE( DIVIDE ([TotalPremium],[PPI Calls]), USERELATIONSHIP('PROD SALES'[Advisor], 'PPI'[ADVISOR]) ), // Default: average over all call times if no or multiple selections DIVIDE([TotalPremium],[PPI Calls]) ) I need to add the following conditions to calculate PPI as well: - Selecting Department Only - Date + Department - Advisor + Date + Department My priority is when selecting a single date to show the correct PPI result. Total Premium selecting the date 5/15: $84,179.52 # PPI Calls from PPI Table for the selected date 5/15 = 142 Total Premium / PPI count = Total PPI Expected Total PPI result: $592.81 TOTAL PREMIUM FOR "Field" Department (from Prod Sales table): $846,573. 08 PPI Count FOR "Field" Department (from PPI table): 1,202 Total Premium / PPI count = Total PPI Expected Total PPI result when "Field" is selected: $704.30 Total Premium for "Call Center" Department (from Prod Sales table): $951,098.02 PPI Count for "Call Center" Department (from PPI table): 1,702 Total Premium/ PPI Count = Total PPI Expected Total PPI result when "Call Center" is selected: $558.81 Expected PPI when no filter is applied: $619.03 Appreciate your help!
USERELATIONSHIP in a measure
I have two tables: 'Deals' and 'Dates' with couple many to one inactive relationships (for example based on 'Deals'[Created Date] and 'Deals'[Won Date]). I ran two queries in Power BI Desktop using DAX Query View: 1. EVALUATE { CALCULATE( COUNT( 'Deals'[Deal ID] ), USERELATIONSHIP( 'Dates'[Date], 'Deals'[Won Date] ), FILTER( 'Deals', 'Deals'[Product] = "XYZ" && NOT ISBLANK('Deals'[Won Date]) ) ) } 2. EVALUATE { CALCULATE( COUNT( 'Deals'[Deal ID] ), FILTER( 'Deals', 'Deals'[Product] = "XYZ" && NOT ISBLANK('Deals'[Won Date]) ) ) } I am getting two different results, which makes me wondering why if there are no outer filters applied and how does calculate work in the bacground to obtain different outputs here. I have went through different 'How does CALCULATE work' articles but could not find one that covers my case. Thank you in advance!
