cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

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

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

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.

Highlighted

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

mcastilloy2k_0-1603986370841.png

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors