Blanks() not automatically filtered in report view


#1

Hi Sam,

I cannot figure out why my table in the report view is not filtering all blank values of Year-over-year (YoY) Profit of products ranked greater than 3.

Below is my code to filter top 3products ranked by largest YoY Profit growth:

Top3_YoY_Product_Profit = CALCULATE([YOY_GP$], 
FILTER(VALUES('Dim-Product'[Brand]),
    IF(RANKX(ALL('Dim-Product'[Brand]),[YOY_GP$],,DESC) <= 3, [YOY_GP$], BLANK())))

The output filters the Top 3 but also shows $0 for products ranked more than 3. I don’t want it to show those products in the report view. Is there a way to do this in dax without the use of a visual filter?

TopN%20issue

My Dim-Product table is connect to my FactSales table via the column name “Item #”. Maybe because I’m filtering by “Brand” and not “Item#” it’s causing this issue :confused:


#2

Place your blank() logic outside of the CALCULATE and FILTER. Make that a zero value

Then write some logic that looks like this. IF( Measure = 0, BLANK(), Measure )

The BLANK() is not working as it should here.

Here’s an example to review