Measure seems to ignore filter in visual

[JWifoe]

Hello all,

I am using Power BI now for some time now but I still would say that I am basically a beginner.

From time to time I encounter something I always have to find some work around but I don't really understand, what and why is happening here.

I searched the documentation, the board and youtube but didn't find an explanation. Maybe someone could be so kind to point me in the right direction.

I made a short, very basic example:

my data model:

I created 2 measures:

( I know that these measures are not exactly best practice, but I think they are ok to explain what my issue is)

Number of Orders =

CALCULATE(COUNTROWS(Orders),Orders[Customer] = MAX(Customer[Name]))

Is Customer =

IF([Number of Orders] > 0, "Yes", "No")

Both measures work fine:

When I use a Slicer and only the measure number of orders the result is as expected:

But when I also put the Measure "Is Customer" in the visual, this happens:

I don't understand why this is happening.

Any feedback will be very much appreciated.

Best regards,

Jens

[JWifoe]

Hi,

thx.

I changed the measure and now it checks whether the row should be evaluated at all (if not, I get Blank(), if yes but no orders, I get a "0") and now it works the way I would like it to.

But neverteheless I will modify my data model.

best regards,

Number of Orders =

VAR NoO =

    COUNTROWS ( Orders )

RETURN

    IF (

        CONTAINS ( Groups, Groups[Groups], MAX ( Customer[Group] ) ),

        IF ( NoO > 0, NoO, 0 ),

        BLANK ()

    )

[DataInsights]

@JWifoe,

This is due to the following:

BLANK() > 0 = FALSE

When the measure [Number of Orders] returns BLANK, it causes the IF statement to resolve to "No". Thus, even though [Number of Orders] is blank for a Name, a row for that Name will appear since [Is Customer] returns "No".

[JWifoe]

Hi, thank you for your answer.

But I still don't understand why Power BI is even doing the calculation for a row that should be filtered out.

To put it maybe in another way: How would I have to change the measure (or is this maybe not possible with a measure and I would have to use calculated column?) in below example so that the visual show only the rows with group a or b (corresponding to the slicer) but also rows with "Number of Orders" = 0.

[DataInsights · Accepted solution]

@JWifoe,

I would need to see your pbix to be sure. Your data model uses a snowflake schema which is not recommended. A star schema works best with Power BI. Can you merge the tables Groups and Customer?

In the measure, you could add a second condition that tests for BLANK:

IF ( [Number of Orders] > 0 && [Number of Orders] <> BLANK(), "Yes", "No" )

Also, look at "strictly equal to" to handle blanks:

https://dax.guide/op/strictly-equal-to/