Forum Discussion

tcoe64's avatar
tcoe64
Regular Visitor
4 years ago
Solved

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

  • 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.