Return all rows from table matching multiple criteria

[Splyn]

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

[Ashish_Mathur · Accepted solution]

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.

[v-lili6-msft · Accepted solution]

hi, @Splyn

You could try this way:

Step1:

Create a measure as below:

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
))

Step2:

Drag fields from Transaction table and measure into table visual.

Of course, you could just drag measure into visual level filter and set it is not blank

By the way, for Ashish_Mathur code, I have applied it into the demo pbix, you could also try it.

Best Regards,
Lin

[Greg_Deckler]

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

[Splyn]

Thanks for the reminder. 

Here is the picture, in text form. 

Transaction table
Store	Brand	Value
1	a	1
1	b	2
1	c	3
1	b	1
1	d	2
2	a	3
2	c	1
2	c	2
2	d	3
3	b	1
3	c	2
3	d	3
3	a	1
3	a	2
3	c	3
4	a	1
4	b	2
4	c	3
4	d	1
4	a	2
Brand Table
ID	Brand
1	b
2	c
Results Table
Store	Brand	Value
1	b	2
1	c	3
1	b	1
3	b	1
3	c	2
3	c	3
4	b	2
4	c	3

[Ashish_Mathur]

Hi,

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

[Splyn]

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. 

[Ashish_Mathur]

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.

[Ashish_Mathur · Accepted solution]

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.

[Splyn]

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

[Ashish_Mathur]

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.