ISBLANK help required please

[josuem]

Hi,
Can you please help me with ISBLANK funtion.
I am calculating how many days is an order late via this formula:

LATE BY DAYS1 = FORMAT(MAXX(‘COHV Planning’,
TODAY() - ‘COHV Planning’[Basic finish date]), “0”)

I created a virtual table to select the orders which are Late with this formula which works fine.
Late Orders =
SUMX(
CALCULATETABLE(
‘COHV Planning’,
FILTER(‘COHV Planning’,‘COHV Planning’[Order]),
FILTER(‘COHV Planning’, ‘COHV Planning’[Work Centre] <> “00384”),
FILTER(‘COHV Planning’, IF(TODAY() > ‘COHV Planning’[Basic finish date], ‘COHV Planning’[Order], BLANK())),
FILTER(ProdOrderStatus,
ProdOrderStatus[Status] = “REL”)

My question is about ISBLANK, i don’t understand it that well.

I wan to not show the orders which are not late with the Late Order formula.
But as you can see there are still showing.

Any help is most Welcome.

[piryani]

You can use calculate formula with condition of blank.

I think it should work. Sam’s following video will help.

Formula syntax, comments, variables

[josuem]

Hi,

Thank you for getting back to me quick.

How would you use calculate here.

I watch the video now. If I use calculate will ISBLANK work? Because I’m this video Sam didn’t exclude blanks.

I’m very new to power bi and have limited time atm.

Thanks.

[piryani]

Just to simplify, if you want to add it as a column, ISerror function is defined in formula guide of Enterprise DNA to work as follows:

ISBLANK Function (DAX)
Checks whether a value is blank and returns TRUE or FALSE.
Syntax: ISBLANK()
Parameter Definition
value The value or expression you want to test.
Return Value:
A Boolean value of TRUE if the value is blank; otherwise FALSE.
Example:
This formula computes the increase or decrease ratio in sales compared to the previous
year. The example uses the IF function to check the value for the previous year’s sales in
order to avoid a divide by zero error.
Sales to Previous Year Ratio:
=IF(ISBLANK(‘CalculatedMeasures’[PreviousYearTotalSales]),BLANK(),(‘CalculatedMeasur
es’[TotalSales‘CalculatedMeasures’[PreviousYearTotalSales])/’CalculatedMeasures’[Prev
iousYearTotalSales])

[SimbaWM]

Hi everyone,

Probably another way to look at a solution would be to look at how the table in the screenshot is being populated for the Order list. Conceptually, that order list should only have orders that meet @josuem’s requirement of “I want to not show the orders which are not late …”

I cannot help much with the above suggested approach unless I see the data model that pertains to the challenge at hand, and probably the source of the Order column that is in the table.

Please share at least these if you would like me or some other people on the forum to have a go at finding a solution with my approach.

[ambepat]

I think the reason those orders are still showing is because of the Basic Finish Date columns/measure. I will create an if condition to make it dependent on the late days measure.

So If(ISBlank([Late Orders]), Blank(), [Basic finish date]).

Let me know if that works or make sense.

[josuem]

Hi Kind People,
Thank you yet again for your responses.

I have now manage to fix this with your help.

First i change this formula by excluding any dates that are less the ZERO. Since this would not be late. Maybe not required but i still did it.

I also changed my formula as per below and used calculate as recommended by Piryani, instead of calculatetable. I guess i was overthinking things, as i wanted to iterate the table to do the calculation but Filter iterates also.

SimbaWM, thank you also.

Below is what i wanted. Displaying the late orders and how many days their late by.

[josuem]

Ambepat, yes it makes now. Cheers.