All,
As Brian has done a spectacular job in getting this far, i’d like to reach out to anyone else who would like to contribute.
As Brian suggested i tried several versions of DAX to utilize the SWITCH function to overcome the nested AND OR statements, below are 2 that i had most success with, although neither is correct.
Level 5 Test 1 =
VAR Prelim =
CALCULATE(
COUNTROWS('Contract Review'),
ALLEXCEPT(
'Contract Review',
'Contract Review'[Review No]
),
FILTER(
'Contract Review',
AND(
SWITCH(TRUE(),
'Contract Review'[Step Number Text] = "Lvl4 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0,
'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0,
'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0, 1
),
'Contract Review'[WF Status] = "Completed"
) = TRUE
)
)
VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
SUMX(
'Contract Review',
'Contract Review'[Hrs to complete] * PrelimBinary8
),
ALLEXCEPT(
'Contract Review',
'Contract Review'[Review No]
)
)
RETURN
Result
Level 5 Test 2 =
VAR Prelim =
CALCULATE(
COUNTROWS('Contract Review'),
ALLEXCEPT(
'Contract Review',
'Contract Review'[Review No]
),
FILTER(
'Contract Review',
AND(
SWITCH(TRUE(),
'Contract Review'[Step Number Text] = "Lvl4 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0,
'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0,
'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
'Contract Review'[ Step Status] = "Skipped" &&
'Contract Review'[WF Status] = "Completed", 0, 0
),
SWITCH(TRUE(),
'Contract Review'[Step Number Text] = "End" &&
'Contract Review'[WF Status] = "Completed", 1,
'Contract Review'[Step Number Text] = "Notify CSR Approved" &&
'Contract Review'[Step Number Text] = "End", 1, 1)
) = TRUE
)
)
VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
SUMX(
'Contract Review',
'Contract Review'[Hrs to complete] * PrelimBinary9
),
ALLEXCEPT(
'Contract Review',
'Contract Review'[Review No]
)
)
RETURN
Result
In each DAX what i believe i need to do is somehow get the VAR Prelim to be > than 1. as we loop through all the contracts. Where the conditions are TRUE for Level 2 thru 4 “Step Number Text” and the associated “Step Status” is “Skipped”, i assign a 0 value in the SWITCH statement. As the IF statement then checks to see if > 1, and assigns a value of 1 for true and BLANK() for false.
Then for each contract review with the SUMX function we intend to gather the matching values when not a Level 2 thru 4 contract review.
Brian was most helpful in supporting me in getting the correct DAX for contract reviews that were Level 2 thru 4, the last remaining piece is getting the elusive Level 5 number.
The table below shows the results from the DAX Level 5 Test
As you can see contract review no 17778 is in the table. This is a Level 4 contract review and should not be part of the Level 5 table, hence i know i have issues with the DAX.
I have also attached a small sample of the database in an Excel format, perhaps this could be uploaded into the pbix file that Brian created, to aid in solving this issue.
Sample Contract Review.xlsx (10.7 KB)
Thanks
J