Using isBlank to return blank when no value provided in a field

[Clint]

Hello,

Trying to use the following as part of an if statement to suppress the return of a RYG indicator but getting the error "A single value cannot be determined for column "Task Finish Baseline Date" in Table Task Baselines.....  I created this as a calculated column as I thought this would avoid this sort of error.  Shouldn't this expression be evaluated row by row against this table?  This would return a single value in this column if so.  Any thoughts on why this is returning this error?
C_Task MS RYG = If(isBlank('Taskbaselines'[task baseline finish date]), BLANK(),

[Cmcmahan]

When calculating a column, try this to get a single value:

C_Task MS RYG = If(isBlank(EARLIER('Taskbaselines'[task baseline finish date])), BLANK(),

[Clint]

Thank you @Cmcmahan  - this returns the error "earlier/earliest refers to an earlier row context which doesn't exist"

[Cmcmahan]

Ugh. 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?

[v-frfei-msft]

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.

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

[Clint]

Hello Cmcmahan,

I was able to work in the Selected Value option you outlined in the following calculation

C_Task MS RYG2 =

If(SELECTEDVALUE('TaskBaselines'[Task Baseline Finish Date],BLANK()),

If('Tasks'[Task Finish Variance Days]<=7,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_6p...." ,

if('tasks'[Task Finish Variance Days]>=21,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_2p...." ,

"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_1p...." )))

And this doesn't throw any errors but it also doesn't return an indicator when it should (per the embedded screenshot)

All of the Milestones but the first one should show a RYG indicator in the last column in the table (The status column is calculated using a straight "if" calculation w/o trying to supprese the indictaor when there is no target date.

[Cmcmahan]

Hmm... that looks like it should work.  So we get to start debugging.

Can you create a few test measures and put them into the table for me?

Test1 = SELECTEDVALUE('TaskBaselines'[Task Baseline Finish Date])

Test2 = 'tasks'[Task Finish Variance Days]

If you don't get the expected values for these, then we know what part of the main C_Task MS RYG2 indicator we need to examine/fix.

[Clint · Accepted solution]

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...." , if(MAX('tasks'[Task Finish Variance Days])>=21,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_2p...." , "https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_1p...." )))

[Cmcmahan]

Nice man! That looks good!  Glad you were able to figure it out

[Clint]

Thank you!  I could not have done it w/o your help.  As a rank newbie to DAX, I find it invaluable to be able to work thru a solution w/someone so thank you.

[Clint]

Hi CMcMahan,

So, I had an opportunity to work w/this some more today and found - if I use a measure in the TaskBaselines table, I am getting closer to the right result.

M_Task MS RYG2 =

If(

Not isBlank(Max('taskbaselines'[Task Baseline Finish Date])),

(SELECTEDVALUE('TaskBaselines'[Task Baseline Finish Date],

If(max('Tasks'[Task Finish Variance Days])<=7,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_6p...." ,

if(max('tasks'[Task Finish Variance Days])>=21,"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_2p...." ,

"https://Aristocratgaming.sharepoint.com/sites/pwa/_api/Projectdata/_layouts/15/inc/PWA/images/cf_1p...." )))))

This returns nothing for the cell that should be blank but, for the other cells that should have an indicator, it is returning the baseline date.

[Clint]

I will give this a shot.  Thank you!

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