Forum Discussion
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
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
9 Replies
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?
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
