Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...
Solved! Go to Solution.
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]))
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.
Hi @Clint,
You are possibly missing closing ' after both table names:
Try:
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:
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")
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:
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:
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:
The new calc looks like this:
Hi @MariaP ,
Thanks so much for the reply. The culprit is definitely the addition of the If statement that brings in the bullets.
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".
The calc for Demand divided by Capacity is:
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.
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]))
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.
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...
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |