Forum Discussion
Report level filter by count of values
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.
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
2 Replies
v-frfei-msftCommunity Support
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
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.
