Forum Discussion
Helper VUsing isBlank to return blank when no value provided in a field
Hi CMcMahan,
After futzing w/this a bit, I finally got it to work correctly with this DAX:
Updated PR MS Indicator calc
M_Task MS RYG2 =
If(
isBlank(Max('taskbaselines'[Task Baseline Finish Date])),
BLANK(),
If(MAX('Tasks'[Task Finish Variance Days])<=7,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_6p.png" ,
if(MAX('tasks'[Task Finish Variance Days])>=21,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_2p.png" ,
)))
Helper VThank you Cmcmahan - this returns the error "earlier/earliest refers to an earlier row context which doesn't exist"
Resident RockstarUgh. I hate messing with EARLIER/EARLIEST.
The root problem here is that when you evaluate this
ISBLANK('Taskbaselines'[task baseline finish date])DAX is returning a list of values. You're asking "Is this value blank?" and giving the function a whole column of values. DAX gives up and tells you there's an error. What you need is a way to select one of those values. Depending on the context you're in, you may be able to use SELECTEDVALUE, RELATED or some other aggregation function to get one value out of the list.
Is the table you're adding this column to the same one the value is in?
- Clint
Hi Cmcmahan ,
No, it's in the Taskbaselines table and column is in the Tasks table. I put an aggregator in (Max) and the calculation doesn't throw an error but it doesn't return the right result either. The RYG indicators are still showing up when the baseline (target) is blank
I can try moving it to the Taskbaselines table to see if that makes a difference.
- Clint
What's confusing is, if I drop this field (task baseline finish date) into a flat table -- along w/the project name aned task name -- it returns just a single value (field set to don't summarize automatically by BI). So I'm not sure why it is returning a list of values? I'm putting this in a matrix table, could that be the difference in what is being returned between the flat table and matrix?
- Cmcmahan
Ah, yes. Matrix vs table.
Every value in a matrix is assummed to be some sort of aggregation of data, grouped by the current row(s) and column(s). Values in a table are just pulled directly from a table. You were able to create the calculated table with no issues, but now the matrix is trying to aggregate those values.
You should be able to use this measure instead to return the proper value with the code:
SELECTEDVALUE('Taskbaselines'[task baseline finish date],BLANK())This will select the first value from whatever the current filter context is, or null if there are no options or multiple options.
If you start having problems with totals showing up that you don't want, you can see my recent adventure fighting with a matrix here: https://community.powerbi.com/t5/Desktop/Only-Total-Select-Values-in-a-Matrix-amp-Formatting-Help/m-p/710741#M343163
- Clint
I will give this a shot. Thank you!
v-frfei-msftCommunity Support
Hi Clint ,
Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
- Clint
Hi Frank,
It is proprietary in teh sense it is live data that refers to real products/dates from my company. All I'm really trying to do is show a RYG indicator when the variance between a milestones baseline and estimated dates is greater than certain parameters. The trick is, if there is no baseline date set, I want to suppress the RYG indicator (as otherwise it shows as "green" since the variance is calculated as 0. The problem is, BI thinks there is more than one value for"task baseline finish date" and expects me to aggregate this value.