Report level filter by count of values

[ivancans]

Hi,

For the purpose of this issue, let's say that I have two tables:

Google Analytics (GA table) with columns:

Source/Medium ID

and Income table with columns:

ID Income(EUR)

Both tables have a relationship (based on ID). 

What I need is to filter out data in report view by count of source/medium, to be precise, there needs to be at least 30 records of any specific source/medium, because otherwise, we are going to have income outliers that disturb my final result - a bar chart of average income by source/medium. The problem is, I don't know how to do because there are so many source/medium variations that it can't be done by hand, like using COUNTAX or CALCULATE+FILTER when I would indicate specific source/medium combination or a handful of combinations for example. 

[v-frfei-msft · Accepted solution]

Hi @ivancans,

Please check the following steps as below.

1. Create a calcualted table using the formula.

Table = CALCULATETABLE(GA,FILTER(GA,CALCULATE(COUNTROWS(GA),ALLEXCEPT(GA,GA[Source/Medium]))>=30))

2. Create relationship between the calculated table and the income table.

3. Create a measure as bleow.

income = IF(ISBLANK(MAX('Table'[Source/Medium])),BLANK(),CALCULATE(SUM(Income[Income(EUR)])))

For more details, please check the pbix as attached.

Regards,

Frank

[ivancans]

Thank you, @v-frfei-msft !

It worked. I had a question though. How can this be done in query mode/view? I read that in general it is better to do data transformations in power query mode rather than in data view or report view with DAX.