Forum Discussion
Need help: To add same description in two different tables
This is one Report with 2 tables:
| REPLENISH | ||
| DESCRIPTION | COLOR | MEASURE |
| NU529 | BLACK | 1 |
| NU529 | GREY | 2 |
| NU529 | NAVY | 5 |
| P195 | BLACK | 1 |
| P196 | NAVY | 2 |
This table i have Measure based on the quantity on this particular shop, based on qty <= 5.
Here i have filter to select Shops.
| MATRIX TABLE | |||||
| STORES | 1 | 2 | 3 | 4 | 5 |
| DESCRIPTION | 6 | 5 | 4 | 7 | 3 |
| 5 | 2 | 1 | 5 | 2 | |
| 3 | 2 | 3 | 3 | 1 | |
| 5 | 7 | 9 | 2 | 8 | |
| 7 | 6 | 5 | 1 | 0 | |
| 5 | 5 | 3 | 7 | 9 |
In this table i need the description from the table 1, and the all quantity they carry for all the shops.
3 Replies
- johnbasha33
Super User
To combine the information from the "REPLENISH" table with the "MATRIX TABLE" while filtering the "MATRIX TABLE" based on the quantity on a particular shop, you can use a calculated column or measure in Power BI. Below is an example of how you can achieve this using DAX measures:
1. **Create a Calculated Column in "REPLENISH" Table:**
This column will concatenate the "DESCRIPTION" and "COLOR" columns to create a unique identifier for each item.```DAX
UniqueID = REPLENISH[DESCRIPTION] & "_" & REPLENISH[COLOR]
```2. **Create a Measure in "MATRIX TABLE" to Filter Based on Quantity:**
This measure will calculate the sum of quantities for each description and color combination where the quantity is less than or equal to 5.```DAX
TotalQty <= 5 =
CALCULATE(
SUM('MATRIX TABLE'[DESCRIPTION]),
FILTER('MATRIX TABLE', 'MATRIX TABLE'[DESCRIPTION] = SELECTEDVALUE('REPLENISH'[UniqueID]) && 'MATRIX TABLE'[STORES] <= 5)
)
```3. **Add Description from "REPLENISH" Table to "MATRIX TABLE":**
Drag the "DESCRIPTION" column from the "REPLENISH" table to the "MATRIX TABLE" visualization to display the description alongside the quantities for each shop.By following these steps, you can integrate the information from the "REPLENISH" table into the "MATRIX TABLE" while filtering the quantities based on the specified condition. This allows you to analyze the quantities carried by each shop for the items specified in the "REPLENISH" table.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!I think I did not explained it very well, my apologies. Thanks for your time. Please can you do it once again.
Style no. ATTRIBUTE Store 1 Store 2 Store 3 Store 4 Store 5 Store 6 NU529 DGRY 1 3 4 8 2 0 NU529 NAV 2 5 0 3 9 4 NU529 RED 3 12 8 5 4 2 P209 MAR 5 2 7 6 0 3 P209 BLK 1 2 3 3 6 1 I want a matrix table like the above.
1. I need to filter one store, (for quantity =<5) it should only display those styles. BUT
2. The other stores should display all the quantity they have on those styles displayed.I hope you have solution for this.
Thanks in advance for your kind cooperation.
I think I did not explained it very well, my apologies. Thanks for your time. Please can you do it once again.
Style no. ATTRIBUTE Store 1 Store 2 Store 3 Store 4 Store 5 Store 6 NU529 DGRY 1 3 4 8 2 0 NU529 NAV 2 5 0 3 9 4 NU529 RED 3 12 8 5 4 2 P209 MAR 5 2 7 6 0 3 P209 BLK 1 2 3 3 6 1 I want a matrix table like the above.
1. I need to filter one store, (for quantity =<5) it should only display those styles. BUT
2. The other stores should display all the quantity they have on those styles displayed.I hope you have solution for this.
Thanks in advance for your kind cooperation.
