Filter blank values on matrix

[Anonymous]

Hello,

These are my tables:

And these are my data:

I need to create a measure to filter Costs greater than the Budget, to do so, I created a filter table:

And created the following measure:

IF(
    HASONEVALUE('Filters'[Description]);
    IF(
        VALUES('Filters'[Description]) = 1;
        IF(
            SUM(Costs[Value]) > SUM(Budget[Budget]); 
            1;
        )
    )
)

It works correctly:

Now I use the measure as a visual level filter than only shows values not null. But since I am using a Matrix, If I filter the visual to not show null values for my filter and the table is showing only the first level of data it removes blank data from my second level. It is expected, but I want to avoid it. 

Look these images:

Cost Center C has a greater value than the budget, but it does not happens for Acc A. 

But, since I am on second level of my matrix, it is not a problem. But now, look at the first level of the matrix and remove blank filter activated.

The value for Cost Center decrease to 150 because that the filter removed Acc A from the cost center. But I do not want it. If the matrix is on it first level, it must show the full value (155). How can I do this?

[v-huizhn-msft]

Hi @Anonymous,

From what I know, when you add the measure as a visual level filter, which will filter the whole matrix, no matter which level is display. For your requirement, you want to remove the second level value, while the first level still stay the original one, it's impossible to date. Thanks for understanding.

Thanks,
Angelia

[Anonymous]

Hi @v-huizhn-msft,

Thanks for helping me. Do you know any other approach to solve my problem - if the cost is bigger than the budget, show it, else, does not show it? 

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,


For your requirement, you want to filter the second level of a report, while the first level stay the original value, which is unsupported to date, you can create an idea here.

Best Regards,
Angelia

[Anonymous]

I tried to check if my table is filtered by Account and it works, only if the visual level filter is not activated:

IF(
  HASONEVALUE('Filters'[Description]);
  IF(
    VALUES('Filters'[Description]) = 1;
    IF(
      ISFILTERED(Account[Description])
      IF(
        SUM(Costs[Value]) > SUM(Budget[Budget]);
        1;
        BLANK()
      ) 
      IF(
        CALCULATE(SUM(Costs[Value]) > SUM(Budget[Budget]); ALL(Account)); 
        1;
        BLANK()
      )
    )
  )
)

But, if I activate the visual level filter to not show Blank values for My Measure, independently of the filter level - Cost Center or Account - the function ISFILTERED always returns TRUE. So I can not accomplish my needs that way. Can someone help me?