Forum Discussion
Impactful IndividualFilter Table by Column Values Associated with Selected Slicer Values
You can achieve this with a disconnected table, created in either Power Query or DAX. Here's the DAX calculated table:
SlicerNames = SUMMARIZE ( Customers, Customers[Name], Customers[FamilyID] )Create the Name slicer using this calculated table. It should not have a relationship with the Customers table.
Next, create this measure. The TREATAS function captures the selected value(s) in the slicer, and treats them as if they were from the Customers table.
FamilyID Measure = CALCULATE ( MAX ( Customers[FamilyID] ), TREATAS ( VALUES ( SlicerNames[FamilyID] ), Customers[FamilyID] ) )In the table visual, add ID and Name (from Customers), and the measure.
You're one step from success. 🙂 In the V1 (Route 1) pbix, I changed the relationship between Dim Customer Visual and Dim Customer to 1:*. You have to change the cardinality before changing the cross-filter direction. Since Dim Customer Visual is a clone of Dim Customer, it is actually a 1:1 relationship, but you have to define it as 1:* (single cross-filter direction) for this solution to work.
Here's the result after making the change above:
I added another member to the Dim Customer family: Dim Customer Visual Root. This is a calculated table that follows the same pattern as Dim Customer Visual. Notice that the relationship is between Dim Customer Visual[Root Parent ID] and Dim Customer Visual Root[Customer Key]. This allows parents and children to be included in Total Sales, while displaying only parents in the visual.
DAX for the calculated table:
Dim Customer Visual Root = FILTER ('Dim Customer', 'Dim Customer'[IsRootParent] = 1 )Measure:
Total Sales by Root Parent = CALCULATE ( SUM ( 'Fact S1'[Sales] ), ALL ( 'Dim Customer' ), // clear all filters from the slicer VALUES ( 'Dim Customer'[RootParentID] ), // get the RootParentID from the slicer KEEPFILTERS ( 'Dim Customer Visual Root' ), USERELATIONSHIP ( 'Dim Customer Visual Root'[CustomerKey], 'Dim Customer Visual'[RootParentID] ), USERELATIONSHIP ('Dim Customer Visual'[CustomerKey], 'Dim Customer'[CustomerKey] ) )Use Dim Customer Visual Root columns in the Table 2 visual:
Impactful Individual
I realize it’s typically bad form to tack another question onto an already resolved thread.
However, this question requires an understanding of the situation already described in this thread; so I am posting it here.
Challenge #2
A second table has also been requested which groups Table 1 by RootParentID.
- It must sum the totals for [Total Premium] (a.k.a. “Total Sales” in the screenshots below) for the entire family.
- Each family’s totals should appear on only 1 row of Table 2.
- Only the Root Parent CustomerName in each family should be displayed (WHERE IsRootParent = 1).
Can you instruct me on how to create Table 2?
Doing what you mentioned in thread #4 of the post gets me very close to the solution, but not quite. As you advised:
- Table 1 uses 'Dim Customer Visual' fields
- Table 2 uses 'Dim Customer' fields
RESULTS:
- Table 2 does indeed group Table 1. So this is good.
- The only problem is that the CustomerName shown in Table 2 is always the one selected, when it needs to be the RootParent of each family, instead.
For example, when "Jennifer" & "Tony" are selected:
- Jennifer is a child of Susan in Family 3 (RootParentID = 3).
- For this reason, Susan's name should appear in Table 2, for Family 3 - not Jennifer’s.
- Tony is the RootParent in Family 5 (RootParentID = 5).
- In this case, Tony appears in Table 2, which is good since he is the Root Parent of his family.
- However, if his child Grace had been selected, then Table 2 would incorrectly show Grace (child record).
NOTE: The latest mock file is V3 at the following location:
https://drive.google.com/drive/folders/13HqZmd_S7YEcTLWUr2txNN-7ysRug3ZX?usp=sharing
As always, thank you.
- DataInsights
Super User
Try this measure. I added a filter argument for IsRootParent:
Total Sales by Root Parent = CALCULATE ( SUM ( 'Fact S1'[Sales] ), ALL ( 'Dim Customer' ), VALUES ( 'Dim Customer'[RootParentID] ), KEEPFILTERS ( 'Dim Customer Visual' ), USERELATIONSHIP ( 'Dim Customer Visual'[CustomerKey], 'Dim Customer'[CustomerKey] ), 'Dim Customer Visual'[IsRootParent] = 1 )The visual needs to use fields from Dim Customer Visual, in order to break the link between the slicer and visual (otherwise, only the customers selected in the slicer would appear in the visual).
- WinterMist
I have made the following 2 changes:
- Visual table "Table 2 - Family Totals by Root Parent (DCVisual) is now pulling all fields (except the measure) from 'Dim Customer Visual'.
- Measure "Total Sales by Root Parent" has been updated to add the last argument:
- 'Dim Customer Visual'[IsRootParent] = 1
RESULTS
- The CustomerName now shows the Root Parent name correctly in Table 2. This is great! Thank you!
- However, adding the new parameter to the measure unfortunately breaks the sum of Total Sales in Table 2. It now simply shows the sales for each root parent (not the root parent's entire family as it was correctly doing before when we were using fields from Dim Customer & not Dim Customer Visual (see screenshot visual table below "Table 2 - Family Totals by Root Parent (DC)").
Thanks again for all your time.
- DataInsights
I added another member to the Dim Customer family: Dim Customer Visual Root. This is a calculated table that follows the same pattern as Dim Customer Visual. Notice that the relationship is between Dim Customer Visual[Root Parent ID] and Dim Customer Visual Root[Customer Key]. This allows parents and children to be included in Total Sales, while displaying only parents in the visual.
DAX for the calculated table:
Dim Customer Visual Root = FILTER ('Dim Customer', 'Dim Customer'[IsRootParent] = 1 )Measure:
Total Sales by Root Parent = CALCULATE ( SUM ( 'Fact S1'[Sales] ), ALL ( 'Dim Customer' ), // clear all filters from the slicer VALUES ( 'Dim Customer'[RootParentID] ), // get the RootParentID from the slicer KEEPFILTERS ( 'Dim Customer Visual Root' ), USERELATIONSHIP ( 'Dim Customer Visual Root'[CustomerKey], 'Dim Customer Visual'[RootParentID] ), USERELATIONSHIP ('Dim Customer Visual'[CustomerKey], 'Dim Customer'[CustomerKey] ) )Use Dim Customer Visual Root columns in the Table 2 visual: