ISBLANK in IF

[Anonymous]

Hi,

I have a measure (""Real_Orders" - connected with Database, future dates do not have a value) and a column ("Target_Orders" for each date in the next 6 months) and I want to subtract them, but only if "Real_Orders" are not empty. If there are no orders tracked (future dates), the subtraction should be 0.

I tried it like this:

IF(ISBLANK([Real_Orders]);0;([Real_Orders]-SUM(Target_Orders)))

Unfortunately it doesn't work and it seems that he is not replacing the subtraction with 0.

Thanks in advance.

Micha

[dedelman_clng]

Depending on how your [Real Orders] measure is created, you may need to test on [Real Orders]=0 instead of BLANK().

Hope this helps.

David

[Anonymous]

Unfortunately not. It's the same.

The measure (real_orders) is based on DISTINCTCOUNT.

[dedelman_clng]

can you share your measure code and data model?

[Anonymous]

Hi,

I'm not sure what information you need (I'm new working with Power BI).

Measure: "Real_Orders"=DISTINCTCOUNT(Database[ID])

> Every unique ID is an order, if a customer selected more than one product, the ID is the same.

Data Model: Do you mean, which data/tables are given and their relations?

Database: As said: "Real_targets" results from these data

Date: master excel file (date, week, month, year, etc.) - relation between Database and Date via date (dd:mm:yyyy)

Target: excel file (target_orders, date) - relation between Target and Date via date (dd:mm:yyyy)

I hope that helps understanding.

[v-jiascu-msft]

@Anonymous

Hi,

DISTINCTCOUNT counts Blank as a unique value. So DISTINCTCOUNT always return value bigger or equal to 1. You can try this formula. 

Measure =

IF ( ISBLANK ( Database[ID] ), 0, [Real_Orders] - SUM ( [Target_Orders] ) )

Best Regards!

Dale Cui

[Anonymous]

@v-jiascu-msft

Thank you. 

Database(ID) is no measure so I tried:

IF ( ISBLANK(DISTINCTCOUNT( Database[ID] ), 0, [Real_Orders] - SUM ( [Target_Orders] ) )

Still no change - still no 0 for every date with no value.

[v-jiascu-msft]

@Anonymous

Hi,

It's embarrassing. Database[ID] can't be used directly in there. ISBLANK(DISTINCTCOUNT( Database[ID] )) will never be BLANK. That is why there are no 0s. Maybe this would work. Please have a try.

Measure =
IF (
    COUNTBLANK ( Database[ID] ) > 0,
    0,
    [Real_Orders] - SUM ( [Target_Orders] )
)

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft

Hi,

no change, unfortunately.

Maybe my measure "Real_Orders" is not that good to be based on DISTINCTCOUNT?! But I think there has to be a solution with those given circumstances...

Best Micha

[v-jiascu-msft]

@Anonymous

Hi,

"no change" means there is still no "0"s? The latest formula does nothing with Real_orders. Could you post a sample or share the PBIX file please?

Best Regards!

Dale

[Anonymous · Accepted solution]

@v-jiascu-msft

Thank you for your help.

I solved it now a different way. I just filtered all future dates an subtract real orders with target orders.