Forum Discussion
Helper IIIHelp with slicer for multiple Columns in Same Table (To and From)
Hello!
I am struggling with a Report I am building for controlling the stock of some of our users.
We can have multiple types of stock movements, and the users can receive and give stock in between them.
The issue is that I have a table with the stock movements in between users, one line for each movement, with the following columns:
To: Gives us the user that is receiving the stock
From: Gives us the user that is giving out the stock
Quantity: Number of items given from user 1 to user 2.
Please find an example below:
| Movement_id | User_From | User_to | Quantity |
| 1 | Warehouse | User 1 | 100 |
| 2 | User 1 | User 2 | 50 |
| 3 | User 1 | User 3 | 50 |
| 4 | User 3 | Warehouse | 20 |
I would love to have a slicer that, when chosen a specific user could show us the movements of stock that user receives (To) and gives out (From). So, when choosing User 3 on the Slicer, I would only see:
| Movement_id | User_From | User_to | Quantity |
| 3 | User 1 | User 3 | 50 |
| 4 | User 3 | Warehouse | 20 |
Any ideas?
Thanks a lot!
Rate - There are pros/cons to each of the 2 solutions:
1. My solution does not duplicate rows in the fact table, but it creates a non-intuitive (and likely slow) filter.
2. Ashish_Mathur 's solution contains duplicate rows, but creates a natural relationship/filter. Duplicate rows means that the fact table will only produce the correct results if you apply a filter.
There is a 3rd solution as well, which would create a larger User table instead of duplicating rows in the fact table and also create a natural relationship/filter:
1. Create the modified User Slicer Calculated Table:
User Slicer 2 = UNION( DISTINCT(SELECTCOLUMNS(Table2,"User",[User_From], "User Combo", [User_From] & "->" & [User_to])), DISTINCT(SELECTCOLUMNS(Table2,"User",[User_to], "User Combo", [User_From] & "->" & [User_to])) )2. Create a new Calculated Column on your fact table:
User Combo = [User_From] & "->" & [User_to]
3. Create a Relationship between your fact table and User Slicer table, on the User Combo column. It will need to be a Many-to-Many relationship, with User Slicer filtering the fact table.
4. Create a Measure:
Quantity Measure 2 = SUM(Table2[Quantity])
All 3 of these solutions produce the results you are looking for - you will need to consider / test the trade-offs between them. The disadvantage of the 3rd solution is that it uses a larger dimension table, with a more granular user key in the fact table. This means the index of the User Combo will have more distinct values and therefore be less efficient than Ashish_Mathur 's Value column.
Cheers!
Nathan
5 Replies
Rate -
You could try the following:
1. Create a disconnected parameter table (no relationship to your fact table) that will be your slicer. In this case, you could create the following Calculated Table:
User Slicer = UNION( SELECTCOLUMNS(VALUES(Table2[User_From]),"User",[User_From]), VALUES(Table2[User_to]) )2. Create the following measure for Quantity. The idea is that it filters to only include the relevant rows. Note: In a table visual, if all measures are blank, then the row won't show up.
Quantity Measure = var user = SELECTEDVALUE('User Slicer'[User]) return CALCULATE( SUM(Table2[Quantity]), FILTER( Table2, OR( Table2[User_From] = user, Table2[User_to] = user ) ) )Cheers!
Nathan
- Ashish_Mathur
Super User
Rate - There are pros/cons to each of the 2 solutions:
1. My solution does not duplicate rows in the fact table, but it creates a non-intuitive (and likely slow) filter.
2. Ashish_Mathur 's solution contains duplicate rows, but creates a natural relationship/filter. Duplicate rows means that the fact table will only produce the correct results if you apply a filter.
There is a 3rd solution as well, which would create a larger User table instead of duplicating rows in the fact table and also create a natural relationship/filter:
1. Create the modified User Slicer Calculated Table:
User Slicer 2 = UNION( DISTINCT(SELECTCOLUMNS(Table2,"User",[User_From], "User Combo", [User_From] & "->" & [User_to])), DISTINCT(SELECTCOLUMNS(Table2,"User",[User_to], "User Combo", [User_From] & "->" & [User_to])) )2. Create a new Calculated Column on your fact table:
User Combo = [User_From] & "->" & [User_to]
3. Create a Relationship between your fact table and User Slicer table, on the User Combo column. It will need to be a Many-to-Many relationship, with User Slicer filtering the fact table.
4. Create a Measure:
Quantity Measure 2 = SUM(Table2[Quantity])
All 3 of these solutions produce the results you are looking for - you will need to consider / test the trade-offs between them. The disadvantage of the 3rd solution is that it uses a larger dimension table, with a more granular user key in the fact table. This means the index of the User Combo will have more distinct values and therefore be less efficient than Ashish_Mathur 's Value column.
Cheers!
Nathan
RateHello Anonymous and Ashish_Mathur !
Really, really, grateful for your help!! Quick, thorough and really smart solutions! I am amazed and really grateful.
I have triedthe three solutions and they work seemlessly! Again, I can't thank you enough. Having to choose one upon the three, I think my favourite solution is the one proposed by Ashish_Mathur. I don't have that much data and I am always forcing a filter to be selected.
Again, thanks a lot and have a great week!
Cheers,