IsFiltered is not working to show blanks at specific levels of a Matrix (pivot table) visualization.

[realdanielbyrne]

I am creating a new report that uses a matrix (pivot table) to show

• Equipment, grouped by category, and the hours ran for each piece of rented equipment.
• I do not want to show the hours or date the equioment was rented the at the upper category level.

I have used the following nearly identical line of code in another report, and it works perfectly.  In this new report, ISFILTERED on the category level returns 'TRUE' when it should return false. 

Total Hours = IF(ISFILTERED(Utilization[Equipment]),ROUND(SUMX(Utilization,Utilization[Hours]),0))

I used similar logic on the 'Rent Start' column, as the date for 'Rent Start' only applies to individual pieces of equipment and not to the entire category.

Rent Start = if(ISFILTERED(Utilization[Equipment]),MIN(Utilization[udRentStart]))

Also there are no page level or report level filters, nor any other table or slicer on the page besides a date slicer, nor any other pages in the report as it is brand new. Here is a screenshot of the resulting errored visualization.  As you can see from the 'IsFiltered Test' column, the IsFiltered function is returning 'True' at the category level where it should be False.

IsFiltered Test = ISFILTERED(Utilization[Equipment])

Here is a screenshot of the data in the matrix

Here is a screen shot of the other report I referenced in which ISFILTERED is working as expected to hide the manufacturer at the category level, but to show it at the equipment level.  This is the behavior sought.

Make = if (ISFILTERED(EquipmentCostRevenue[Equipment]),FIRSTNONBLANK(EquipmentCostRevenue[Manufacturer],1))

Can someone pleae help me understand why the same pattern, in two different reports, behaves differently?   Or is this simply another bug in PowerBI and DAX?  If this is another bug, what is the more consistent approach to use besides using ISFILTERED?

Thank you,

[MarcoPessina]

Hi,

the reason why ISFILTERED returns True even if there aren't direct filters is because it is used in the same table where you have the column that you are evaluating (Utilization[Equipment]). That means that the evaluation context of Is Filtered Test column is filtered by all the columns of the table. Thus, each row Is Filtered.

If you remove Utilization[Equipment] from your visual you'll get ISFILTERED = False.

For the same reason your second visual works correctly as EquipmentCostRevenue[Equipment] is not included and thus not filtered by all the other columns.

Cheers,

Marco

[realdanielbyrne]

Thank you @MarcoPessina for your response, but unfortunately you are incorrect in your assumption.  Both reports source the columns 'Equipment' and 'Category' from the same table, albeit different PowerBI tables for both reports.

In the current broken report, the table is named 'Utilization'.  In the prior working report the table is called 'EquipmentCostRevenue'.  Don't let the names fool you.  The source of the data for the matrices in both reports come from the same PowerBI table and coincidently the same underlying table in the database as well; although that should have no bearing on this problem as the data for both reports is gathered with a SQL query.  

However if that were the case, then I submit that the documentation should be updated to note this obscure condition. 

[amitchandak · Accepted solution]

@realdanielbyrne 

I did not get completed. But some time dealing grouping/row/col/view by  better to check for isinscope

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

This is a good example: https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Also, refer

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[realdanielbyrne]

Thank you @amitchandak that fixed it!  For all those browsing for the solution, I simply changed ISFILTERED, with ISINSCOPE in my equation, and everythng worked beautifully.

Rental Start = if (ISINSCOPE(Utilization[Equipment]),MIN(Utilization[udRentStart]))

Of course that begs the question as to the inconsistency of ISFILTERED, but I'l leave that question for a PowerBI scholar to investigate.

Thank you,

-db