Forum Discussion
Nested IF statements evaluate by Matrix
Hi There - long time enthusiast first time posting
I'm trying to use nested if statements to evaluate a count based on measures that correspond to the row and column of the matrix. The goal is to get the IF statements to identify if a project should have a given gate or not, based on its [Project_Size]. The conditions for this are defined in the IF statement below.
First, the count
The first measure counts the number of gates that a given project has, for the current period/snapshot (referred to as VAR lat)
TaskStartDate_Lat = //Return latest snapshot task start date
VAR lat = ProjectTasksSP_Full_TC[LatTaskSnapshot]
RETURN
COALESCE(CALCULATE(COUNT(ProjectTasksSP_Full_TC[TaskStartDate]),ProjectTasksSP_Full_TC[Snapshot Date]=lat),0)
This works fine and correctly counts 1 or 0 gates as needed for conditional formatting.
Second, The harder part
Here VAR cnt refers to the count described above VAR lat refers to the most recent period/snapshot.
I have included // comments for each IF statement that appears to not be working...
Gate_Checker = //Return latest snapshot task start date
VAR lat = ProjectTasksSP_Full_TC[LatTaskSnapshot]
VAR cnt = ProjectTasksSP_Full_TC[TaskStartDate_Lat]
RETURN
IF(cnt=1 && ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF Lite" && ProjectTasksSP_Full_TC[P3MF_New_Gate_Lat] IN {"Gate 0","Gate 3","Gate 4","Gate 5"},1,//working
IF(cnt=0 && ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF Lite" && ProjectTasksSP_Full_TC[P3MF_New_Gate_Lat] IN {"Gate 0","Gate 3","Gate 4","Gate 5"},0,//not working
IF(cnt=0 && ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF Lite" && ProjectTasksSP_Full_TC[P3MF_New_Gate_Lat] IN {"Gate 1","Gate 2"} ,"N/A",//not working
IF(cnt>0 && ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF Lite" && ProjectTasksSP_Full_TC[P3MF_New_Gate_Lat] IN {"Gate 1","Gate 2"} ,"Unreq Gate",//not working
IF(ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF Agile","Agile",//working
IF(ProjectTasksSP_Full_TC[Project_Size_Lat]="BAU","BAU",//not working
IF(ProjectTasksSP_Full_TC[Project_Size_Lat]="P3MF","X","E")))))))
The result appears to evaluate the first and fifth if statement but not the others???
Columns: Use column 'ProjectTasksSP_Full_TC'[P3MF_New_Gate] a calculated column
Project Size: Is a column from a related table 'Project Core' - which is related with a unique ID
Any help greatly appreciated - I'm stumped.
Hi tcoe64 ,
This related with the way the syntax is calculated, in order to evaluate things correctly can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
1 Reply
- MFelixSuper User
Hi tcoe64 ,
This related with the way the syntax is calculated, in order to evaluate things correctly can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.