Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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,
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.
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.
Thanks for the response! For some reason, I cant seem to view the power query code. Could you copy it into this thread?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |