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!
Calculate percentages with filters and ponderate values
Hi community. I have a table to which I want to calculate percentages depending on the filters I apply. The conditions for the filters are the columns “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS”. The value on which I want the percentage to be calculated is the column “Valor ponderado”. The two characteristics that I have not been able to resolve are: Not all records in “Valor ponderado” have the same values but can be 0,1, 2 or 3 so a simple formula of adding the values and dividing by the number of values does not work for me to calculate the percentage. The other point is that there are records that have no response (null) so they should not be included in the percentage calculation. I would like to know how to calculate the percentages to consider the records with different values (0, 1, 2 or 3) and not to consider the null values for the calculation. The result would have to be that when you filter by “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS” the percentages are calculated. Thank you very much. Regards Table with data
Sum only Values where another Column has Data
Harder than it looks: I have this simple table like below: I want to calculate how we're performing based on the goal in a Card. So I need to Sum only the goal values where the Gross Adds column is not blank(GAs are in the past and Goals are for the whole month). In theory this should work, but it puts a total for all the months previous rather than what is selected in the slicer. What's strange is if I replace [GAGoal] with [Gross Adds] in the measure below it correctly adds up the Gross Adds for the current month selected in the slicer. GoalTotalCurrent = VAR LastDaySelection = LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] ) VAR CurrentRange = DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ('Calendar'[Calendar Date] ), LastDaySelection ) RETURN IF ( LastDaySelection >= MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [GAGoal], CurrentRange )) I've tried things like this, but it results in blank: SumGoals = IF(SUMX('Subscriber Activity',[Gross Adds])<>Blank(),SUMX('Subscriber Activity',[GAGoal])) So it shouldn't sum 1/8,1/9,or1/10 Date Goal Gross Adds 1/1/2025 5 6 1/2/2025 7 5 1/3/2025 8 7 1/4/2025 7 7 1/5/2025 4 3 1/6/2025 3 2 1/7/2025 2 1 1/8/2025 5 1/9/2025 5 1/10/2025 5How to calculate percentage of each group out of total number of distinct rows
Hello! I have a table that looking at type of insurances that customers have and customers could select more than one type, however ultimately, I would like to create a visual in my dashboard that shows the percentage of responses for each insurance type OVER the DISTINCT number of respondents. For example, if 301 people selected 'Medicaid' (this includes folks who selected only Medicaid or selected Medicaid and other types of insurance, for the numerator all I care about is that they selected 'Medicaid') out of 861 DISTINCT individuals who responded to the survey, how do I get a percentage that reflects non-distinct count per group OVER a distinct number of IDs? Here is my visual thus far: ^The percentages you see at the bottom of each square in the tree map DO represent what I am looking for, for example 301/861 folks= 34.96% selected Medicare, however I had calculated that in R before exporting to Excel and then importing into Power BI. I want to calculate this however IN Power BI if possible instead because when I try to filter based on age, educational level, etc. as seen on the right-side of the screen, those percentages do not change. Here is my table that the visual is based on- column Q1= ID of individual, column insurance= insurance type, total_insurance= distinct count of Q1 (I calculated this in R) & count_insurance=count of insurance by group /total_insurance, again calculated in R. Any suggestions for how to calculcate total_insurance & count_insurance in PowerBI rather than bringing them over from R would be much appreciated, thank you! 😊 How do I recreate these last two columns exactly as they are but using a calculation made in PowerBI rather than R?^ Attached is a PowerBI workbook at this link in Dropbox https://www.dropbox.com/scl/fi/fmtzurp0k6jkl36bk7qll/insurance_long.pbix?rlkey=i6ph2ji5ll9hwicv14yjtcg5q&st=upw9xiot&dl=0 . The table in my screenshot above is for the insurance_long table in the PowerBI workbook. The sheet_na table in the PowerBI workbook contains the ID "Q1" and the age category variable used as a filter on the far right of the visual "Q4_cat" and then there is also a table for race as that also serves as one of the filters. Thank you!
Calculate new employees per month
Hey there, I am trying to create a visual with the new employees for each month. Positions can occur several times but not at the same time. For example Position P-10057. ValidFrom ValidTo FullTimeEquivalent PositionId 31.12.2023 31.12.2154 0.2 P-10016 01.12.2012 31.12.2154 1 P-10017 18.01.2016 31.12.2154 1 P-10055 01.09.2017 31.12.2154 1 P-10056 01.02.2023 31.12.2023 1 P-10057 30.06.2024 14.10.2024 1 P-10057 14.11.2023 29.02.2024 1 P-10058 31.07.2024 31.12.2154 1 P-10058 01.08.2021 04.06.2024 1 P-10059 01.08.2022 30.07.2025 1 P-10060 01.08.2022 31.07.2025 1 P-10061 15.08.2022 13.08.2025 1 P-10062 01.08.2023 30.07.2026 1 P-10063 01.08.2023 08.05.2024 1 P-10064 01.08.2021 04.06.2024 1 P-10065 I use the following Dax: FTE_Onboarding = CALCULATE( SUM(PositionWorkerAssignmentsV2[FullTimeEquivalent]), FILTER( PositionWorkerAssignmentsV2, PositionWorkerAssignmentsV2[ValidFrom] >= MIN(DateTable[Date]) && PositionWorkerAssignmentsV2[ValidFrom] <= MAX(DateTable[Date]) ) ) The datetable goes from 2022 to 2026. So the missing FTE before 2022 are correct. However I am get the following result: FTE_Onboarding Year Month 3 2022 August 1 2023 Februar 2 2023 August 1 2023 November So the FTE from Dec 23 and the two FTE from 24 are missing. Looking forward to your answers 🙂
Why is my measure not evaluating to 1?
Hello Forum, I have a series of Measures I am trying to use to identify the first instance of a Customer placing an Order. I need to accomplish three things: Identify the Minimum Order Date across a series of dimensions Return "1" for rows where the order falls within the date context Sum all rows where "1" was returned I am able to accomplish one and two, but my third Measure is giving unexpected results. Why are the highlighted values appearing as blank? __NewAccounts_A_FirstOrderDate: __NewAccounts_A_FirstOrderDate = var maxContextDate = MAX( Dates[Date] ) RETURN CALCULATE( MIN( Sales[Delivery Date] ), ALLEXCEPT( 'Sales' , Customer[Customer No.], --Customer[Customer], Supplier[Supplier], Location[Branch] ), Sales[DeliveryDate] >= DATE( YEAR( maxContextDate ) - 1, 1, 1), Sales[Delivery Quantity] > 0 ) __NewAccounts_B_FirstOrderInContext: __NewAccounts_B_FirstOrderInContext = var maxContextDate = MAX( Dates[Date] ) return IF( [__NewAccounts_A_FirstOrderDate] >= DATE( YEAR( maxContextDate ), 1, 1) && [__NewAccounts_A_FirstOrderDate] <= maxContextDate, 1, 0 ) __NewAccounts_C_DistinctNewCustomers: __NewAccounts_C_DistinctNewCustomers = CALCULATE( DISTINCTCOUNT( Customer[Customer No.] ), FILTER( Sales, [__NewAccounts_B_FirstOrderInContext] = 1 ) )DAX measure calculate filter returning incorrect result
Hello DAX experts, Please refer the screenshot. I have a Measure name "CurYear" which equals 2024. When I use "CurYear" in another Measure "Act.YTD.CurrentYear", the CurYear is returning both 2023 and 2024 instead of only 2024. The result for 2024 Act. should be 3,106 but the measure is also summing year 2023 making the total to 14,764, which is not correct. My goal is to let [Fiscal Year] dynamically pick the result (i.e. 2024) from Measure "CurYear". Can someone please help fix this? Thank You!
