Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clint
Helper V
Helper V

Help with If Statement

Hello,

 

I am trying to write the following IF statement (in a Measure) 

M_Allocations RYG = If('ResourceDemandTimePhaseDataSet[Demand divided by Capacity]<.7 , 'Projects[PWA URL] & "/_layouts/15/inc/PWA/images/cf_6.png", "N")

 

but keep getting the msg

"The following syntax error occurred during parsing: Invalid Token, line 1, offset 69, 'Projects[PWA URL] & "/_layouts/15/inc/PWA/images/cf_6.png", "N")

 

I have used a variation of this basic If Statement to return a RYG indidcator so I know it can work but I'm not entirely sure where I've made the mistake here...

1 ACCEPTED SOLUTION
MariaP
Solution Supplier
Solution Supplier

Hi @Clint,

 

Located the file with the data model I needed. There are 2 simpler tables you could look at - 'ResourceCapacityData' and 'ResourceDemandData'. These have 'Capacity' and 'Demand' for each ResourceId and TimebyDay. I have used these tables and using ResourceName (I believe this is  your team name shown)

 

Demand divided by Capacity = DIVIDE(SUM(ResourceDemandData[Demand]), SUM('ResourceCapacityData'[Capacity])) 
and the arrows as shown before (you could use a SWITCH if preferred) :
Arrow colour Column2 =
IF (
[Demand divided by Capacity]> 1.0 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/reddiamond_orig.png",
IF ( [Demand divided by Capacity] > 0.5 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png" ,
IF ( [Demand divided by Capacity] >0.1 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/greenarrowicon_orig.png" , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png"

) ))
 Your SUMX is calcuating/iterating for every row.
 
See if you can use locate these 2 tables to simplify as when I first found them they contained all I needed.
 
Tables to useTables to useOne entry per ResourceOne entry per Resource
 
 
Here is the Data Model also,
Data ModelData Model
Maria

View solution in original post

13 REPLIES 13
MariaP
Solution Supplier
Solution Supplier

Hi  @Clint,

 

You are possibly missing closing ' after both table names:

 

Try:

M_Allocations RYG = If('ResourceDemandTimePhaseDataSet'[Demand divided by Capacity]<.7 , 'Projects'[PWA URL] & "/_layouts/15/inc/PWA/images/cf_6.png", "N")

Thank you Maria. I no longer get Syntax errors so you're suggestion appears to have fixed that. I am running into an issue w/the ,'Projects'[PWA URL] part of the string.  BI now says "A single value fr column 'PWA URL' in table 'Projects' cannot be determined.  This can happen when a measure or formula refers to a column that contains many values without specifiying an aggregation such as min, max, count or sum to geta single results.

 

This is the variation of this If statement that does work:

FC KPO Ind = if(Projects[FC Variance]<=0,[PWA URL] & "/_layouts/15/inc/PWA/images/cf_6p.png",if(Projects[FC Variance]>0,[PWA URL] & "/_layouts/15/inc/PWA/images/cf_2p.png"))

It is in the 'Projects' table so I don't have to fully qualify the table name for PWA URL.  

 

Not sure why this measure is not working w/the same syntax???

 

M_Allocations RYG = If('ResourceDemandTimephasedDataSet'[Demand divided by Capacity]<.7,'Projects'[PWA URL] & "/_layouts/15/inc/PWA/images/cf_1.png","N")

MariaP
Solution Supplier
Solution Supplier

Hi @Clint,
From memory you can try creating this as a calculated column not as a measure so that each result is calculated for the many entries in ResourceDemandTimephased table
Don't recall the tables off hand as I haven't used for a while, but there may be another Resource table to try without the many to many entries.
If you can send small sample pbix file I can assist further.

Also if original question had been answered please mark as such.

Cheers,
Maria

Hi Maria,

 

I did try it as a calculated column and it doesn't return anymore errors but I had to put an aggregate function like (Distinct) or MAXA in front of Projects[PWA URL]. Unfortunately, it is now returning weird results; meaning each team is doubled,  You can see the table as it should be w/o the RYG bullets I am trying to add and w/the modified If statement

Current If Statement:

C_Allocations RYG = If('ResourceDemandTimephasedDataSet'[Demand divided by Capacity]<.7, DISTINCT('Projects'[PWA URL]) & "/_layouts/15/inc/PWA/images/cf_2.png","N")

Allocations no bulletsAllocations no bulletsAllocations with bulletsAllocations with bullets

MariaP
Solution Supplier
Solution Supplier

Hi  again @Clint

 

Uisng your ranges just refer directly to the URL in your code:

Arrow colour Column = 
IF (
       [Demand divided by Capacity]=  0.25 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/reddiamond_orig.png",
        IF ( [Demand divided by Capacity] =  0.50 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png"   ,
               IF ( [Demand divided by Capacity] <= 0.07 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/greenarrowicon_orig.png" , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png"
               
) ))

Replace values with your correct numbers. Replace the URL with your URL. Create this as a calculated column. 

Make sure your Data Category for the Arrow colour Column is set to type 'Image URL' 

Hi Maria,

 

Thanks so much for your help.  The bullets now show as they're supposed to but the teams are still not showing correctly.  They are doubled/tripled up now and I don't understand what is causing this.  The table should look like this:
allocations no bullets.PNG

But as soon as I add replace the conditionally formatted RYG field (it's just the team's percent allocation vs capacity number) w/the calculated column to display a bullet instead, I get this:
allocations with bullets.PNG

The new calc looks like this:

C_Allocations RYG = If('ResourceDemandTimephasedDataSet'[Demand divided by Capacity]<=.7, "https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_1p....",
IF('ResourceDemandTimephasedDataSet'[Demand divided by Capacity]>.9,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_2p....",
 
The visual is relative date restricted by "Show items when the value is this month.
MariaP
Solution Supplier
Solution Supplier

Hi @Clint,

There are multiple entries for your result for "demand divided by capacity". I would drag extra columns into the table to see where the problem is doubling up , could be same date having the same project entered multiple times in the resourceDemandTimephased table, could be the formula...could be also if you have a baseline set for projects and the baseline changes, you will see multiple entries in project so you may need to set report to the latest baseline by filtering eg. ProjectBaseline=0. Even pulling the "Allocation Status" from another table can change the doubling up.
I would need to see the data model and formulas to work it out further and explain. If you can send through small sample pls do. See how you go...
Happy to help as much as I can.
Thanks.

Hi @MariaP ,

 

Thanks so much for the reply.  The culprit is definitely the addition of the If statement that brings in the bullets. 

allocations with numbers.PNG

Without it, the table shows the correct values for April

 

I left the Demand divided by Capacity in then added the bullets back in.  When I do this, it breaks out the total allocation into two to 3 parts (I would guess this depends on the assignments for the team resources).   I tried to use SUMX to aggregate the allocations but I get the message "too few argument passed to SUMX".

allocations with numbers and bullets.PNG

The calc for Demand divided by Capacity is:

Demand divided by Capacity =
DIVIDE([Demand], SUM('ResourceTimephasedDataSet'[Capacity]))
Demand is a calculated column:
Demand = SUMX(FILTER(ResourceDemandTimephasedDataSet,ISBLANK(RELATED(Resources[Resource Id]))=FALSE()),ResourceDemandTimephasedDataSet[Resource Demand])
 

Forgot to add, the break-outs seem to add up to the total allocation as indicated in the first table but the bullets are mostly incorrect  for the specified percentages in the break out table.

MariaP
Solution Supplier
Solution Supplier

Hi @Clint,

 

Located the file with the data model I needed. There are 2 simpler tables you could look at - 'ResourceCapacityData' and 'ResourceDemandData'. These have 'Capacity' and 'Demand' for each ResourceId and TimebyDay. I have used these tables and using ResourceName (I believe this is  your team name shown)

 

Demand divided by Capacity = DIVIDE(SUM(ResourceDemandData[Demand]), SUM('ResourceCapacityData'[Capacity])) 
and the arrows as shown before (you could use a SWITCH if preferred) :
Arrow colour Column2 =
IF (
[Demand divided by Capacity]> 1.0 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/reddiamond_orig.png",
IF ( [Demand divided by Capacity] > 0.5 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png" ,
IF ( [Demand divided by Capacity] >0.1 , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/greenarrowicon_orig.png" , "http://imagestorageforpmo.weebly.com/uploads/2/8/7/4/28748783/yellowarrowicon_orig.png"

) ))
 Your SUMX is calcuating/iterating for every row.
 
See if you can use locate these 2 tables to simplify as when I first found them they contained all I needed.
 
Tables to useTables to useOne entry per ResourceOne entry per Resource
 
 
Here is the Data Model also,
Data ModelData Model
Maria

Hi @MariaP ,

 

Thank you so much for your time and effort in helping me.  I followed what you did and was able to confirm the Demand vs Capacity calc came out the same as the one I had.  I found however, I had to make both the new Demand/Capacity metric and the If Statement calc Measures to get them to calculate and display correctly.    You can see now everything is working correctly in my proofing table here...
allocations with correct bullets.PNG

MariaP
Solution Supplier
Solution Supplier

Hi @Clint,
Looks great!
I know there are white .PNG images in PMO also that you could use as indicators in your if statement where there is no percentage result e.g for Automation, SCM if that fits in with your business rules.
Pleased you have resolved. Well done.
Maria

Thank you @MariaP ,

 

Couldn't have done it w/o your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.