Exclude members from a table if doesn't have activity on another table

[Anonymous]

Hi everyone!

I'm having issues trying to figuring out how to solve this.  I have two fact tables, one with the advertiser activity (clicks, impressions, revenue) and another one with the advertiser budget.  Both tables are related to an advertiser and date dimensions.  The report is showing the advertiser performance along with its budget. The main issue is that I want to show only the data for advertiser with activity on the selected date range, and I don't want to show the budgets from advertiser with no activity on that range.  When I refer to no activity, I mean advertisers without at least one impression.

On the table visuals, I had success by filtering the sum of the impressions to be greater than 0.  But I'm not having success filtering on other visuals, like bar charts or gauges.  I'm currently using DirectQuery, and I've already tried to use the merge functionality, but the facts are too big to use that (millions of rows on the activity and at least 1 million on the budgets.  I don't want to create a view on the source database.  I've tried DAX without success.

I'm attaching a PBIX with an example, consider that the data is only representative, I have thousands of records per day for the activity.

https://1drv.ms/u/s!Ao4n4pOyN6L3gYFWqBbHy6tVbouuRw?e=R5szMd

Thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , Assuming table budget has a measure budget and activity ahs activity measure

sumx(Budget, if(isblank([activity]), blank(),[Budget]))

[Anonymous]

This worked fine, just found that my issue is related to the budgets.  The table has the budgets per day, and I was using the budget for the current day for an advertiser, for the whole month.  The problem is that some advertiser don't have activity all days, or at least the current day, so the budget is excluded.  As my time dimension has  a month year value I was thinking about creating something for the budgets per month, instead by year, but again, I don't want to create a view on the database.

[Anonymous]

Actually, disregard my previous comment.  I think I am closer to find a solution, using the idea from the formula that you gave me.  As I only want to get the budgets of the advertisers if they had activity on the selected date range, I've ended used the following DAX, the issue is that it works on a table, but the summarized value is wrong and also includes all budgets if add them to a gauge.

Budget = IF(SUMX(FILTER('Fact Activity', 'Fact Activity'[Advertiser Id] = [Advertiser Id]),[Revenue])>0,SUM('Fact Budget'[Last Monthly Budget]),BLANK())

As you can see, the values are filtered but the sum is wrong.  Compare the two last columns