Help with slicer for multiple Columns in Same Table (To and From)

[Rate]

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!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

@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

[Rate]

Hello @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,

[Ashish_Mathur]

You are welcome.

[Anonymous]

@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