User Profile
devesqdeves
Helper II
Joined 5 years ago
User Widgets
Contributions
Re: Aggregate column values per Client Id in a matrix
lbendlin But thats what i have in the measure , Measure_= CALCULATE( COUNTROWS('DW FCTEndOfYearEndings'), FILTER( 'DW FCTEndOfYearEndings', [FilterDateHCFACCurrentDate] = 1 && NOT( CONTAINS( 'DW FCTEndOfYearEndings', 'DW FCTEndOfYearEndings'[CandidateId], CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1)) ) ) ) ). What should i do differently?Re: Aggregate column values per Client Id in a matrix
lbendlin Oh i see , thanks a lot for your help and for your time. If you dont mind just helping me with another subject that is getting my head rolling and i dont understand why, i would really appreciate. - I want to count the rows of DW FCTEndOfYearEndings , where [FilterDateHCFACCurrentDate] = 1 and the 'DW FCTEndOfYearEndings'[CandidateId] is different from the DW LeaveDayDim'[CandidateId] (with no relationship) where [FilterDateLeavesCurrentDate]=1) - This is the measure i am using to count , but is giving me blank CountRows = CALCULATE( COUNTROWS('DW FCTEndOfYearEndings'), FILTER( 'DW FCTEndOfYearEndings', [FilterDateHCFACCurrentDate] = 1 && NOT( CONTAINS( 'DW FCTEndOfYearEndings', 'DW FCTEndOfYearEndings'[CandidateId], CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1)) ) ) ) ) Measures : FilterDateHCFACCurrentDate = VAR _table = if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(), ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) >= TODAY() || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK()) && MAX ('DW FCTEndOfYearEndings'[TrueStartDate]) <= TODAY()), ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) >= _date || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK()) && MAX ('DW FCTEndOfYearEndings'[TrueStartDate]) <= _date )) RETURN IF (_table, 1, 0 ) ----------------------------------------------------------------------- FilterDateLeavesCurrentDate = VAR _table = if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(), max('DW LeaveDayDim'[Day])=TODAY(), max('DW LeaveDayDim'[Day])=MAX ( 'Date'[Date] )) RETURN IF (_table, 1, 0 ) Can you take a look please ?Aggregate column values per Client Id in a matrix
Hello! I am having a hard time dealing with this problem, really hope that someone can help me out. -------------------------------------------------------------- Main goal is to be able to have a slicer with client id that filters the whole matrix , but when not using the slicer (when the selectedvalue(clientid)=blank()), i have to see the sum approveds for all clients , per manager id. Context: - For each ManagerId , i have client ids that have a TotalYearApproved, depending on the client. - My goal is to aggregate the values of the TotalYearApproved column per client id, to be like this : Being all aggregated , it gives 23. The values per month are aggregated correctly , but the TotalYearApproved column isnt. So i have a matrix table : With the measure : AggregatedApproveds = VAR _table = ADDCOLUMNS( SUMMARIZE('DW DimGlobalManagerApproved','DW DimGlobalManagerApproved'[TotalYearApproved], 'DW DimGlobalManagerApproved'[ClientId],'Date'[MonthYear]), "MonthY", CALCULATE(SUM('DW DimGlobalManagerApproved'[Approved])) ) RETURN sumx(_table,[MonthY]) ------------------------------------------------------------------------------------------------------------ If i remove the clientId, the matrix stays like this, it doenst sum the values and only shows the distinct values : The TotalYearApproved column already calculated before coming to powerbi : I dont really know what to do in this case. Thank you all for your patience and attention, RicardoRe: Creating a Measure that checks if a value exists on another table in a Date Range
I tried this : ExistProjectMeasure = if(ALLSELECTED( 'DW FCTOnboarding'[CandidateId]) in CALCULATETABLE(VALUES('DW fctstarting'[CandidateId]),FILTER('DW FCTStarting','DW FCTStarting'[StartDate]>MIN('Date'[Date]))) ,1,0) Update: For what ive seen and searched , the problem is on the part before the IN function , its not selecting all the CandidateId from Onboarding when comparing with CALCULATETABLE , it is only selecting the CandidateId within the selected Week..
