Include all filters in a measure (calculate function)

[Anonymous]

Hi everyone,
 
is there a possibility to include every (ie. all) relevant filter(s) in a measure?
The expression for my measure is as follows:
IF(ISBLANK(COUNTA('Input'[OrderNo])),0,COUNTA('Input'[OrderNo])),
With this I am counting the number of orders.
 
I have another field/column ([Year_Month]) where the year and month of the order is given. Furthermore the orders are grouped into different order-categories (Field/column [Category]).
 
Actually, I have applied a filter to the visual where I would like to use the measeure. The aim is that only orders of the current year and some certain categories appear. Hence, the [OrderNo] should just be counted in consideration with the filters. At the moment, when the filter is applied for the year 2020, the results show correct mounts of orders for 2020 but also "0" orders for the previous years (this is probably due to the ISBLANK condition of my expression above). However, I do not want to see the 0 from previous years, as I just want to see the year 2020.
 
So I tried the CALCULATE function. But which function or condition do I have to use for Filter1, Filter 2 etc. so that all active filters for the visual or the page are included in my measure?
 
CALCULATE(IF(ISBLANK(COUNTA(Input[OrderNo])),0,COUNTA(Input[OrderNo])),????)
 
I tried with ALLSELECTED but this needs to exclude at least one filter. So I need your help please.
 
Many thanks in advance!

[wdx223_Daniel]

IF(COUNTA('Input'[OrderNo]),COUNTA('Input'[OrderNo]))

[Fowmy]

@Anonymous 

Had difficulty understanding it but if you want to exclude all filters try one of the following as the last filter for the CALCULATE function, 

ALL() or ALLSELECTED()

CALCULATE(
    IF(ISBLANK(COUNTA(Input[OrderNo])),0,COUNTA(Input[OrderNo])),
    ALLSELECTED()
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

Thanks for your reply @Fowmy 

Unfortunately, it seems as if my problem was explained quite confusing by me. Actually I want to include (NOT exclude) all the filtes in my dax-measure.

When I am just using the following sequence, it does not work as I am expecting:

IF(ISBLANK(COUNTA('Input'[OrderNo])),0,COUNTA('Input'[OrderNo])),

I also tried the following, but this did not work either:

CALCULATE(IF(ISBLANK(COUNTA(Input[OrderNo])),0,COUNTA(Input[OrderNo]))

Are there any tricks to maintain/include all the filters?

Many thanks!

[Fowmy]

@Anonymous 

IF(ISBLANK(COUNTA('Input'[OrderNo])),0,COUNTA('Input'[OrderNo]))

The above formula that you used should include all initials filters applied on your report as you are not modifying any filters in it. If you are not getting the result that you are expecting, check the filter pane to see if any filters are applied there and remove them. 

you can use this formula on a new page and check as another option. 

the information you provided is not enough to understand your file well. 

if the above suggestion doesn't work, you will have to share a sample PBIX file by attaching the link after saving it in OneDrive or any other cloud storage. 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

@Fowmy 

Thanks for your support!

Unfortunately, it does not work as I expected.

I created a sample model which you can access via the link below. Please have a look at the second sheet named "number of orders_current year accumulated".

You can see a matrix there. In general, I want to count the number of orders by counting the field "OrderType" of the "Input" table - grouped within the matrix by "Year_Quarter" and "Product Level 4".

As I want to show 0 instead of BLANK, when there are no orders, I created the DAX-measure named "calc_number_of_orders": 

IF(ISBLANK(COUNTA(Input[OrderType])),0,COUNTA(Input[OrderType]))

My first issue is that the zeros "0" are not visible in every blank field of the matrix. I am expecting a zero "0" in every blank field ie. in every field, where no orders with "OrderType"="N" were done Do you have any idea where there is a mistake?

My second issue is the one I tried to solve earlier in this post:

I am using filters for this specific page and I want them to be included in the measure. The first filter for the field "OrderType"="N" seems to work correctly. However, I am also using a filter for the field "Period_Year" of the table "year" as on this page, I want only see the current year 2020. For the matrix I would be expecting to only see the quarters of the year 2020. However, when I use the measure, it does also show the the quarters of the year 2019 (and it counts every field with zero "0"), although I am applying the filter for "current year". I also tried to put the "current year" filter directly to the matrix visual, but this did not work either.

For more details please look into the model - I hope my problems are clear now for you.

https://1drv.ms/u/s!AtDThKqVrUlwwm7FXJKxvJj9zr7e?e=rVcF2z