My filter does not working

[New_be]

Hi expert,
I have a simple question (shoulod be simple, but I cannot get the answer).

I have 3 tables. 2 dimensions (Calendar & area) and 1 fact table.


From matrix table, I wanted to created a visual like below with reporter at row, and YearWeek as column and YES if there is a title registered in DAB table. But with my current measure, when I filter on the plant, for example MY, it will show all reporters. It should only return reporter under plant MY. But now it shows all. Below is my measure;

YesOrNo Gemba TEST =

VAR RegistrationCount =

    CALCULATE(

        COUNT('DAB Action List'[Title])

    )

RETURN

    IF(RegistrationCount > 0, "YES", "NO")

Visual that I desired. If I click on MY, it will show only MY registration. If JAP, it will show JAP only.

[OwenAuger · Accepted solution]

Hi @New_be

From what I understand from your post, I believe the sources of this unwanted behaviour are that:

1. When RegistrationCount is BLANK, the expression

IF ( RegistrationCount > 0, "YES", "NO" )

returns "NO" (since, in DAX, BLANK = 0 for the purpose of comparisons).

2. When Power BI generates the DAX query for the matrix visual, the three columns Reporter, YearWeek and Plant (which "filter" a given cell of the matrix) are all from different tables, so non-existing combinations are not automatically excluded by auto-exist.

Putting these together, the problem arises because:

• Combinations of Reporter/YearWeek/Plant that don't exist in 'DAB Action List' (expanded table) will still appear in the query generated by the visual.
• RegistrationCount will be blank for those particular rows, but the measure convertes this to a nonblank values "NO".
• So the visual will contain values for Reporter that do not relate to the filtered Plant.

Generally, converting blank values (resulting from aggregation of a table) to nonblank values goes against the optimizations of the DAX engine and can produce unexpected results, in this case returning results for combinations that don't make sense.

Possible solutions:

1. If you are happy to return BLANK rather than "NO", then change the measure to:

YesOrNo Gemba TEST =
VAR RegistrationCount =
    COUNT ( 'DAB Action List'[Title] )
RETURN
    IF (
        RegistrationCount > 0, -- or NOT ISBLANK ( RegistrationCount )
        "YES"
    )

Note that this would eliminate any columns or rows from the matrix where only blank values are returned (unless you enable "Show items with no data" on the Row/Column fields).

2. If you still want to "fill in the blanks" and return "NO" for cells where RegistrationCount is BLANK, but only for Reporters that exist in 'DAB Action List' subject to the other filters, you could write a measure like this:

YesOrNo Gemba TEST =
VAR RegistrationCount = COUNT ( 'DAB Action List'[Title] )

-- Determine whether rows exist in 'DAB Action List'
-- within the "overall" Date filter context
VAR ReporterExists =
    CALCULATE (
        NOT ISEMPTY ( 'DAB Action List' ),
        ALLSELECTED ( 'Calendar' ) -- Table containing Column field
    )
RETURN
    IF (
        ReporterExists,
        IF ( RegistrationCount > 0, "YES", "NO" )
    )

This measure only returns a nonblank result if rows exist in 'DAB Action List' for the current row of the matrix visual. You could modify the logic in ReporterExists as needed to handle different conditions.

See this article for some more ideas on this general topic (returning "NO" is equivalent to returning zero in this article):

https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax

Hoping some of this helps. Please post back if needed (a link to a sample PBIX may help) 🙂

Regards