Understanding CALCULATE

[admin_xlsior]

Hi,

Need help to have better understanding on CALCULATE. 

For example this code:

Open critical work orders = CALCULATE(
                                        DISTINCTCOUNTNOBLANK('Work orders'[Work order number]),
                                        FILTER('Work orders',
                                               AND('Work orders'[Critical] = "Yes",
                                                    NOT('Work orders'[Status] IN {"Closed", "Invoiced", "Canceled"})
                                               )
                                        )
)

I'm really still don't understand why we need to use FILTER again in 2nd paramater of CALCULATE function? 

I thought the 2nd parameter is already a filter, and the calculation also in same table.

Thanks,

[v-cazheng-msft · Accepted solution]

Hi @admin_xlsior ,

With CALCULATE, we can evaluate an expression in a defined or modified filter context. With FILTER used in CALCULATE, complex filter conditions, including those that cannot be defined by a Boolean filter expression can be applied. For the example you provided, if the filter defined without using Filter function, a True/False expression contained multiple columns can't be used as a table filter expression. In this situation, we can use Filter to cope with it.

For further understanding of these two functions, you can refer to Using the DAX Calculate and Values Functions and Using the FILTER Function in DAX.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@admin_xlsior , You can use the filter function or you can directly give.

But the filter will allow more complex filter like , it allows measure to filter

Date = Max(Table[Date]) // this will not work without filter