Find a client, who DIDN'T make a purchase of particular product

[D3K]

Hey everyone!

Need help to write a kind of complicated measure for me. Here is the problem.

I have a standard list of tables:

- products list

- clients list

- sales (where we can see a sale of each product to any customer)

Each product has a brand.

So it's need to write such measure, to use it in a visual table, which will leave only those clients, who DIDN'T buy a products of  the selected brand.

For example, as you can see on the picture below, I've selected "Brand 3" and in the visual table there is only 1 client "Michael", because he didn't buy any product of the selected Brand 3.

Any help will be appreciated. Thanks a lot!

[amitchandak · Accepted solution]

Try like

except(summarize(all(client),client_name),summarize(filter(client, client_id in allselected(sales[client_id])),client_name))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[D3K]

Thanks a lot @amitchandak  and @Anonymous 

Both ways are working perfect!

Thank you

[amitchandak · Accepted solution]

Try like

except(summarize(all(client),client_name),summarize(filter(client, client_id in allselected(sales[client_id])),client_name))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous · Accepted solution]

 Create one table without relationship just for filtering purpose

New table= values(table[brand name])

use this column in slicer

Create measure

filter=If(selectevalue(table([brand name]))<> selectevalue(new table [brand]),1,-1)

add this measure to visual level filter and set it to 1.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...