cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Splyn
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 IV
Super User IV

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
Community Support

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 IV
Super User IV

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


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Splyn
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 IV
Super User IV

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

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 IV
Super User IV

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

Highlighted
Super User IV
Super User IV

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

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 IV
Super User IV

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
Community Support

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors