Home Forums Power Pivot Populating Blanks via Max Fields & Circular Dependencies

Tagged:

This topic contains 3 replies, has 2 voices, and was last updated by  DaxOnaBoat 5 months, 2 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
• Author
Posts
• #11114

DaxOnaBoat
Participant
• Started: 1
• Replies: 5
• Total: 6

I’ve finally run into an issue that I can’t solve between this great website and The Definitive Guide to DAX (yet), I’m hoping a DAXpert here might know the resolution.

I have a dataset that has the columns Employee, WorkDate, Company, HoursWorked, and a bunch of other columns, but some of the rows in Company are blank. I’m looking to populate the blank Company fields using a logic:

• Sum Hours by Employee and YearMonth
• Sum Hours by Employee and YearMonth and each Company.
• If the Sum of Hours by Employee/YearMonth/Company is > (Sum of Hours by Employee/YearMonth)/2
• Then populate the blank with that Company.

And I solved* it in DAX with these calculated columns:

yymmWorkdate=Concatenate(Year(Data[Work Date]),Month(Data[Work Date]))

Sum Hours by Employee and YearMonth:
EmpMonthHours= Calculate(SUMX(‘Data’,’Data'[Hours]),ALLEXCEPT(‘Data’,Data[Employee],Data[YYMMWorkDate]))

Logic Replacement Formula:

=IF (
CALCULATE (
CALCULATE (
SUMX ( ‘Data’, ‘Data'[Hours] ),
FILTER ( ‘Data’, ‘Data'[Company] = 1 )
),
ALLEXCEPT ( ‘Data’, Data[Company], Data[yymmWorkDate] )
)
> ( [EmpMonthHours] / 2 ),
1,
IF (
CALCULATE (
CALCULATE (
SUMX ( ‘Data’, ‘Data'[Hours] ),
FILTER ( ‘Data’, ‘Data'[Company] = 2 )
),
ALLEXCEPT ( ‘Data’, Data[Company], Data[YYMMWorkDate] )
)
> ( [EmpMonthHours] / 2 ),
2,
0
)
)

And then I create a new corrected company column, CorrectedCompany=If(IsBlank(Company),LogicalNewCompany,Company) and my data populated! (A handful of fields are blank, but that is desirable which is why I used >50% instead of a max formula, not wishing to populate a company name if someone has actually spent a little time at many companies)

But then I changed the number of rows in my source data. I deleted rows and got the dreaded Circular Dependency Detected error. If I delete the formulas and then repaste them in Powerpivot, it works again.

I’ve tried a number of things to fix this but don’t know how, or if there’s a better way to fill in this blank fields with this logic. I know this can be solved in SQL, but a DAX based solution would be greatly preferred in this situation.

#11116

infinitex86
Participant
• Started: 3
• Replies: 9
• Total: 12

I think I grasp the problem but can you share a sample workbook?

#11117

DaxOnaBoat
Participant
• Started: 1
• Replies: 5
• Total: 6

Yes, I’ll add it in after I create one without real data 🙂

#11134

DaxOnaBoat
Participant
• Started: 1
• Replies: 5
• Total: 6

I wasn’t able to duplicate the error with fake data, but I’m looking for the “right” way to produce the column “CorrectedCompany”. If this result can be done in a measure, that’s okay works, I couldn’t find a way to make an If(IsBlank(…)) replacement work in a measure.

EmpMonthHours and CompanyMonthHours are the two columns that produce errors that I’m looking for the “right” solution too.

Thanks for taking a look and any potential solutions you or anyone might know of.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.