Filtering Power BI Report Issue

[John1234]

I'm having some trouble getting Power BI to filter the way I want it to for a specific report, and I'm not sure how to go about it.

I have two tables of users: email subscribers, and customers. Customers is (generally) a subset of email subscribers, with the addition of a few people who are customers, but are not email subscribers. 

Our customer table has more information than the subscriber list, for example, most recent order date, address, etc.

What I am trying to do with my report table is make a list of subscribers whose most recent order date was not May 31st (as an example). When I add the subscribers table to the 'rows', and add a filter from the Customers table of "not May 31st", it eliminates any subscribers who are not customers, in addition to those whose most recent orders were May 31st. 

That is, we have 100,000 subscribers and 10,000 customers. Of those, 1,000 had a most recent order of May 31st. We should have 99,000 people in the new table, but instead we have 9,000. 

I have tried using the 'keep rows with no values' option in the report, but it hasn't helped.

I know I can create a column in the 'subscribers' table with most recent order date, but we have quite a lot of columns in the customer table that we could potentially filter on, and I wouldn't want to add new columns for each if it can be avoided.

What am I missing?

[amitchandak]

@John1234 , How are these two tables related. for that work, customers should be on the many side.

one way is to have flag in subscribers table

new column =

var _cnt = countx(filter(customer, customer[email] =subscribers[email] && customer[Date] <> date(2021,05,31)),customer[email])
return
if(isblank(_cnt),0,1)

[John1234]

Hi Amitchandak

Thanks for your answer. The Customers table is on the many side, and filter direction is both. I do want the subscriber list to filter based on the customers table, I just don't want all rows with no value to be filtered out when I try to eliminate a very specific group.

I know I can add columns in the subscribers table to get this done, but this report is used by others as well, and if they have other specific uses (i.e. give me a list of all subscribers except those that ordered X item, or those that purchased a sale item, etc.), I would need to make an individual column for each, and I'm trying to avoid that. 

[v-jingzhang · Accepted solution]

Hi @John1234 

You could create a flag measure to determine whether a subscriber is in the list of customers whose most recent order date is May 31st. Then put this measure in the table visual's filter pane and set show items when the value is 0.

Flag_Measure =
VAR _customers =
    CALCULATETABLE (
        VALUES ( Customers[Customer] ),
        ALL ( Customers ),
        Customers[Most Recent Order Date] = DATE ( 2021, 5, 31 )
    )
RETURN
    IF ( SELECTEDVALUE ( Subscribers[Subscriber] ) IN _customers, 1, 0 )

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.