Filtering slice with query table

[Dave2]

I’m using a query table to filter a slice based on user input in an interactive dashboard. The slice filters an invoice detail table. The query table has three dropdown fields (Staff, Month, and MCP) with corresponding Y/N columns to turn filtering on or off for that parameter. What I am hoping to achieve is a dynamic dashboard that can display data in related views and graphs based on any combination of those parameters, or none at all. In other words, if all filtering were turned off, it would display data for the entire dataset. Currently, it will filter correctly when any two of the three parameters are selected. This is the filter expression I’m using on the slice:

OR(IF([QueryRef].[Filter Staff] = “Y”, AND([QueryRef].[Staff] = [Invoice Data::User ID], [QueryRef].[MCP] = [MCP], [QueryRef].[Month] = [Month]), AND([QueryRef].[Month] = [Month], [QueryRef].[MCP] = [MCP])),

IF([QueryRef].[Filter MCP] = “Y”, AND([QueryRef].[Staff] = [Invoice Data::User ID], [QueryRef].[MCP] = [MCP], [QueryRef].[Month] = [Month]), AND([QueryRef].[Month] = [Month], [QueryRef].[Staff] = [Invoice Data::User ID])),

IF([QueryRef].[Filter Month] = “Y”, AND([QueryRef].[Staff] = [Invoice Data::User ID], [QueryRef].[MCP] = [MCP], [QueryRef].[Month] = [Month]), AND([QueryRef].[Staff] = [Invoice Data::User ID], [QueryRef].[MCP] = [MCP])))

I have already disabled dependent dropdown for the query table/detail view on the dashboard. I’m curious if what I’m trying to do is possible, or if I’m going about it in the wrong way. I am trying to minimize having redundant views on the dashboard.

Thanks

[Marc_Dillon · Accepted solution]

Yes I’ve done this before, it is quite nice.

Another way to do it is instead of the extra Y/N columns, just check for if a column is blank or not.

You seem to be checking the same things multiple times, but you don’t need to.

I would go with the following formula:
AND(
OR( ISBLANK([QA]) , [QA] = [A]),
OR( ISBLANK([QB]) , [QB] = [B]),
OR( ISBLANK([QC]) , [QC] = [C])
)

[Dave2]

Amazing! It works beautifully, thanks.