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 1 week, 1 day 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: 8
    • Total: 11

    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.