filter not working after measure is introduced

[Anonymous]

I display in a matrix visual in Power BI:

Rows:  

• Client[Parent]
• Client[Name]

Values:

• Fact[Revenue]

I also have a filter on another Dimension that I use:  Dimension2[Rep]

It works fine with no issues.

Now, I have added the next measure:

IF (

    ISINSCOPE ( 'Client'[Name] ),

    MAX ( 'Client'[Responsible] ),

    CALCULATE (

        FIRSTNONBLANK ( 'Client'[Responsible], 1 ),

        FILTER (

            'Client',

            'Client'[Client Number (Legacy)] = 'Client'[Client Number]

        )

    )

)

(That is, if the matrix is expanded it just returns the responsible for each client, but when it is collapsed, it will find the Responsible where Legacy=Client Number)

After I added this measure in the matrix; the filter on  Dimension2[Representative],stopped working! Why is it?

[technolog]

A matrix hides a row only when all measures on that row return BLANK. Before you added the new measure, your visual only had Revenue. When the Dimension2[Rep] slicer removed all fact rows, Revenue became BLANK and the client disappeared. After you added your Responsible measure, it keeps returning a value from the Client table even when there are no matching fact rows, so the matrix now shows those rows. It looks like the filter stopped working, but actually the new measure keeps the row visible.

To fix it, make the measure return BLANK when there are no fact rows in the current context. You can do this by wrapping your logic with a check that tests if any fact rows exist. For example, reference your existing Revenue measure, or use COUNTROWS(Fact) to check if there’s data.

Responsible =
VAR CurrentCN = SELECTEDVALUE(Client[Client Number])
VAR BaseResult =
IF(
ISINSCOPE(Client[Name]),
MAX(Client[Responsible]),
CALCULATE(
FIRSTNONBLANK(Client[Responsible],1),
FILTER(
ALL(Client),
Client[Client Number (Legacy)] = CurrentCN
)
)
)
RETURN
IF(ISBLANK([Revenue]), BLANK(), BaseResult)

If you don’t want to depend on the Revenue measure, you can use this variant instead:

IF(CALCULATE(COUNTROWS(Fact))=0, BLANK(), BaseResult)

Also check that in the matrix settings “Show items with no data” is turned off for Client[Parent] and Client[Name]. If it’s on, the matrix will display clients even when all measures are BLANK.

In short, your new measure calculates independently from the fact table and ignores the filter coming from Dimension2[Rep]. By returning BLANK when there’s no fact context, the slicer behavior will look normal again.

If your goal is for Dimension2[Rep] to filter Client directly, you’d need a proper relationship or use TREATAS or KEEPFILTERS to push the filter from the fact table to Client. But for most use cases, adding the BLANK guard is the simplest and cleanest solution.

[Anonymous]

@Anonymous 

I can see what you have done, but I was not able to reproduce the issue for investigation. Can you also provide a sample table with the above text description. Thanks.

Paul Zheng