cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rate Regular Visitor
Regular Visitor

Help 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_idUser_FromUser_toQuantity
1WarehouseUser 1100
2User 1User 250
3User 1 User 350
4User 3Warehouse20

 

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_idUser_FromUser_toQuantity
3User 1 User 350
4User 3Warehouse20

 

Any ideas?

 

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

Highlighted
Super User
Super User

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

@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 5
Super User
Super User

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

@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

Super User
Super User

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

Highlighted
Super User
Super User

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

@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 Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

You are welcome.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 26 members 928 guests
Please welcome our newest community members: