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

Return all rows from table matching multiple criteria

I have a fact table containing sales data of product broken out by store and brand. I have another table containing a list of brands that I wish to retrieve from the fact table. What im trying to do is for each store within the transaction table, return all matching brands from the Brand table only if ALL brands exist within the store. For example

example.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Return all rows from table matching multiple criteria

Hi,

 

Thank you for clarifying and sorry about not reading your question clearly in the first place.  You had alredy clarified my question in your original post.  You may view my Power Query solution in this Excel workbook.  The same can be done in Power BI dektop as well.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Community Support Team
Community Support Team

Re: Return all rows from table matching multiple criteria

hi, @Splyn

Measure = VAR _table=VALUES(Brand[Brand])
var _table2=CALCULATETABLE(VALUES('Transaction'[Brand]),ALLEXCEPT('Transaction','Transaction'[Store])) return
CALCULATE(COUNTROWS('Transaction'),FILTER('Transaction', COUNTROWS(EXCEPT(_table,_table2))=0&&'Transaction'[Brand] in _table
))

 

 

 

 

 

Best Regards,
Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Return all rows from table matching multiple criteria

Seems solvable, would help tremendously if you pasted that data as text. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Splyn Frequent Visitor
Frequent Visitor

Re: Return all rows from table matching multiple criteria

Thanks for the reminder. 

Here is the picture, in text form. 

 

Transaction table  
StoreBrandValue
1a1
1b2
1c3
1b1
1d2
2a3
2c1
2c2
2d3
3b1
3c2
3d3
3a1
3a2
3c3
4a1
4b2
4c3
4d1
4a2
   
Brand Table  
IDBrand 
1b 
2c 
   
   
Results Table  
StoreBrandValue
1b2
1c3
1b1
3b1
3c2
3c3
4b2
4c3
Super User
Super User

Re: Return all rows from table matching multiple criteria

Hi,

 

Store 2 has Brand C.  Why should that not appear in your result?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Splyn Frequent Visitor
Frequent Visitor

Re: Return all rows from table matching multiple criteria

Because brand B is not in store 2. Results should ONLY include brands from the brand table IF ALL brands from the brand table are within the store. 

 

Let me know if more examples or clarification are required. 

Super User
Super User

Re: Return all rows from table matching multiple criteria

Hi,

 

Thank you for clarifying and sorry about not reading your question clearly in the first place.  You had alredy clarified my question in your original post.  You may view my Power Query solution in this Excel workbook.  The same can be done in Power BI dektop as well.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Super User
Super User

Re: Return all rows from table matching multiple criteria

Hi,

 

I have an additional question here.  Let's say you add another brand in the Brand Table.  Now you may want to see only those rows of data from your transaction table where all brands are sold in each store.  In my solution, one will have to go to the back end query and change the filter criteria from 2 to 3.  This is the manual intervention that i wish to avoid.

 

May be someone can help me solve this problem i.e. count the number of brands in the Brands table and then apply that count figure as a filter criteria.

 

Hope someone pitches in.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Splyn Frequent Visitor
Frequent Visitor

Re: Return all rows from table matching multiple criteria

Thanks for the response! For some reason, I cant seem to view the power query code. Could you copy it into this thread? 

Super User
Super User

Re: Return all rows from table matching multiple criteria

Hi,

 

Download and open my Excel file.  Go to Data > Queries and Connections and in the right hand side pane, right click on the query and Edit.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
Community Support Team

Re: Return all rows from table matching multiple criteria

hi, @Splyn

Measure = VAR _table=VALUES(Brand[Brand])
var _table2=CALCULATETABLE(VALUES('Transaction'[Brand]),ALLEXCEPT('Transaction','Transaction'[Store])) return
CALCULATE(COUNTROWS('Transaction'),FILTER('Transaction', COUNTROWS(EXCEPT(_table,_table2))=0&&'Transaction'[Brand] in _table
))

 

 

 

 

 

Best Regards,
Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 343 members 3,176 guests
Please welcome our newest community members: