Forum Discussion
Helper IIIHelp 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 -
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