Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |