Forum Discussion
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.
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
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
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 |
Hi,
Store 2 has Brand C. Why should that not appear in your result?
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.
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.