filtered table with if statement

[Anonymous]

Hi,

I have a report that is quite large, at the bottom of the page there is a transaction detail table so users can see some selected columns from the raw data. 

At the moment it is too large to compute. 

What I want to do is create a filtered table (from slicers and visual crossfiltering on the page) of the columns using some form of if(COUNT(TRANSACTION_KEY) < 50), TABLE, BLANK) but I've not been able to do it. 

any thoughts?

Thanks.  

[Anonymous]

 @Anonymous 
The error occurs when a visual has attempted to query too much data for the server to complete the result with the available resources. You may need to try filtering the visual to reduce the amount of data.

Try create a flag column, and use that column as a slicer or in the filter panel.

 Flag = IF(COUNT([TRANSACTION_KEY]) < 50), "<50" , BLANK) 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

That would still be a static filter would it not?

I got around it by applying a TOP N (50) on transcation key. It looks like this filter is applied first before the rest of the measures are calculated solving the memory issue.

[amitchandak]

if this 50 comings from the front end.

If not then create a filter table using data transformation/edit query. create a copy of the table and remove rows > 50.

Or Try

calculatetable(Table, filter(Table,TRANSACTION_KEY) < 50))

[Anonymous]

So I've got it to work with 

calculatetable(Table, filter(Table,count(TRANSACTION_KEY < 50 )))

But this doesn't generate on the fly. If I extend to 50 to 100k it replicates table as it's larger than the table size, but it doesn't generate when you're using filters within a report page.  

[Anonymous]

Hi, 

yes it's coming from the front end so I don't think I can use edit query/data transformation. 

I don't understand how this would work?

calculatetable(Table, filter(Table,TRANSACTION_KEY) < 50))

I just get this error message

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.