Good day everyone,
I have a problem I am unable to solve, as I am relatively new to PowerPivot. I have some data and two calculated columns that I need in order to do an analysis of the data and provide some PowerPivot tables and graphs. The type of formula used for the 'Status of Initiative' column makes my excel sheet very slow as it is constantly calculating.
I decided I want to move this calculation/formula to the PowerPivot data screen, and not keep it in the excel table. The column 'Status of Milestone', I already managed to convert to a PowerPivot DAX formula, but I don't know what combination of DAX formulas I can use to have the calculation of the 'Status of Inititative' column in the PowerPivot data sheet to do the same thing as that of my excel sheet. I have a list of data (see attachment), where against a Department, Province, and Initiative number I have various milestone Statuses, 1-4 (1 being "Not started", 2 being "Ongoing", 3 being "Completed", and 4 being "Intervention Required").
Collectively the milestone statuses for each initiative, gives me an initiative status (which is what I am looking for). If we have multiple milestone statuses (say 8 as for Western Cape Education), and the statuses of the milestones are all 2, then the initiative status should be 2. If they are all 3, then the initiative status should be 3, and so on. If the statuses of the milestones are a combination (excluding 4), then the initiative status should be 2, else if the combination includes 4, then the initiative status should be 4. The answers should also only be given to lines that are "Actual", and not "Planned".My excel formula looks like this:
=IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D
,D2,T:T,4)>0,4,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,3)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),3,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,2)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),2,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,1)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),1,2))))
The logic behind this formula is this:
1. Look for multiple lines with the same Province / Department / Initiative number
2. Check what the corresponding values are in the milestone status column for each line
3. Apply the rule as given above
4. Return the answer
I know DAX formulas are really powerful and there must be a way to do this. If anyone can help me with this formula, I'll be eternally grateful - I have tried everything!!Thanks and have a wonderful day!Kind Regards,Llewellyn
I have a problem I am unable to solve, as I am relatively new to PowerPivot. I have some data and two calculated columns that I need in order to do an analysis of the data and provide some PowerPivot tables and graphs. The type of formula used for the 'Status of Initiative' column makes my excel sheet very slow as it is constantly calculating.
I decided I want to move this calculation/formula to the PowerPivot data screen, and not keep it in the excel table. The column 'Status of Milestone', I already managed to convert to a PowerPivot DAX formula, but I don't know what combination of DAX formulas I can use to have the calculation of the 'Status of Inititative' column in the PowerPivot data sheet to do the same thing as that of my excel sheet. I have a list of data (see attachment), where against a Department, Province, and Initiative number I have various milestone Statuses, 1-4 (1 being "Not started", 2 being "Ongoing", 3 being "Completed", and 4 being "Intervention Required").
Collectively the milestone statuses for each initiative, gives me an initiative status (which is what I am looking for). If we have multiple milestone statuses (say 8 as for Western Cape Education), and the statuses of the milestones are all 2, then the initiative status should be 2. If they are all 3, then the initiative status should be 3, and so on. If the statuses of the milestones are a combination (excluding 4), then the initiative status should be 2, else if the combination includes 4, then the initiative status should be 4. The answers should also only be given to lines that are "Actual", and not "Planned".My excel formula looks like this:
=IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D
,D2,T:T,4)>0,4,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,3)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),3,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,2)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),2,IF(COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2,T:T,1)=COUNTIFS(B:B,B2,C:C,C2,F:F,"Actual",D,D2),1,2))))The logic behind this formula is this:
1. Look for multiple lines with the same Province / Department / Initiative number
2. Check what the corresponding values are in the milestone status column for each line
3. Apply the rule as given above
4. Return the answer
I know DAX formulas are really powerful and there must be a way to do this. If anyone can help me with this formula, I'll be eternally grateful - I have tried everything!!Thanks and have a wonderful day!Kind Regards,Llewellyn
Last edited by a moderator: