Filter on measure in direct query does not work

[Sophie_MGG]

Hi all,

I am trying to create a measure that contains a filter on another measure.

I'm working in direct query mode (unfortunately the data comes in via API so I cannot work via Import mode), so this makes it a bit more complicated. 

What I am trying to do is the following: I have a list of orders. Some of them are sent the same day (true,1 ), some not (false, 2).

To see true/false I created the following measure:

Measure = CALCULATE(IF('Order'[DLT pick up status]="Order zelfde dag gepickt", true,FALSE()))

Measure 2 = IF([Measure]=TRUE(),1, 0)

 As seen in the screenshot below, this is shown correctly. 

Now I want to have another measure, were I calculate all orders (order_number) were measure 2 = 1.

I've tried the following:

Measure 3 = CALCULATE(DISTINCTCOUNT('Order'[order_number]),'Order'[Measure 2]=1)

But I get the following error message:

I've tried another measure as well, but that outcome sums up to 3, and I expect it to be 2 (2 orders are true), so it seems like the filter is not working.

Measure 4 = IF([Measure 2]=1, CALCULATE(DISTINCTCOUNT('Order'[order_number])),BLANK())

Can anyone please help? 

Many thanks in advance. 

Cheers, Sophie

[v-eqin-msft]

Hi @Sophie_MGG ,

Thanks for your feedback and glad to know your issue has been solved!😀

Please kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Fowmy]

@Sophie_MGG 

Why don't you use a single measure as follows:

OrderCountSameDay = 

CALCULATE (
    DISTINCTCOUNT('Order'[order_number]),
    'Order'[DLT pick up status]="Order zelfde dag gepickt"
)

[Sophie_MGG]

Hi @Fowmy ,

Thanks for your quick reply. I've tried that as well, but get the same error:

Any other ideas? It might not be possible, but in that case I'm wondering why.

Cheers,

[Fowmy]

@Sophie_MGG 
Can you re-try, I just modified the measure. Make sure you are creating a measure, not a Column.
Post a picture of the error with formula if you face the this error

[Sophie_MGG]

HI @Fowmy , I am indeed creating a measure, not a column (not possible in DQ mode)..

I've copied the measure from your first post but get the same error:

Many thanks for all your help. Much appreciated!

[Sophie_MGG · Accepted solution]

Hi @Fowmy 

I found a solution. This measure works:

CALCULATE (

DISTINCTCOUNT('Order'[order_number]), FILTER('Order',[DLT pick up status]="Order zelfde dag gepickt")

)

(I did not use the filter function before)

Many thanks for your help!