Forum Discussion
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 - 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
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,
You are welcome.
