Calculate, Filter, All in calculated column in date table

[Uki]

Hi,

i have generic date table (DateDim) and a table with list of transactions ('orders by item') with related transaction date (order_date) column. I wanted to mark "busy days" in DateDim table to be able to filter out those days for some additional analytics.

I havem added a calculated column in Date dim:

is busy = IF(CALCULATE(SUM('orders by item'[price_gross]) > 10000, filter(all('orders by item'),'orders by item'[order_date] = DateDim[Date])),1,0)

but the part "all('orders by item') seem not to work - if i filter the data by client or any other dimension the sum of "is busy" in given month can change. What am I doing wrong?

[v-yiruan-msft]

Hi @Uki ,

I just create that calculated column "is busy" with same formula, it works well... But it seems it did not work in your scenario based on your description, then could you please provide your expected result and backend logic?

Best Regards

Rena

[Uki]

Hi @v-yiruan-msft ,

This works correctly for the whole set of data, but if I filter the data in "orders by item" by for example a client (another dimension, among many others in 'orders by item"), then only the days when client had +10000 are marked as "busy". 

This is why I am looking for a way to other filters than date in my calculation.

[v-yiruan-msft]

Hi @Uki ,

Could you please provide some samples to describe the incorrect part in form of screen shot in order to make troubleshoting? Since you are creating a calculated column on table DateDim, the returned value of "is busy" should not be affected by the filters.

Best Regards

Rena

[Uki · Accepted solution]

Hi @v-yiruan-msft ,

I got the problem finaly (thanks to you, I have discovered it making a screen for you and seeing that count on days in date table was showing 20 days in a month).

"Since you are creating a calculated column on table DateDim, the returned value of "is busy" should not be affected by the filters."

Actually it does if you have cross filter direction set to "both" in your calendar to facts table relation, as I for some misterious reason had! 

Thanks again
Łukasz

[v-yiruan-msft]

Hi @Uki ,

Thanks for the information. Then whether your problem has been resolved? Is there anything else need help about this thread? If no, could you please mark the helpful post as Answered? Thank you.

Best Regards

Rena

[amitchandak]

@Uki . Try like

is busy = IF(SUMX(filter('orders by item','orders by item'[order_date] = DateDim[Date]),'orders by item'[price_gross]) > 10000,1,0)

[Uki]

@amitchandak  the same effect i'm afraid. This works correctly for the whole set of data, but if I filter the data in "orders by item" by for example a client, then only the days when client had +10000 are marked as "busy".