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
Thanks very much for responding.
Sorry it's been so long for me attempt your recommendation.
So I was able to create the calculated table (DETACHED from model) and the measure as specified.
For the isolated question above, it does work; and I thank you for showing me this.
However, within the larger context of the report in which this problem resides, we cannot use a customer slicer which pulls its values from a table which is not attached to the model.
The customer name slicer MUST be connected to the model. Without connection to the model, the Customer name slicer will not be able to impact any of the other existing visuals (which there are many).
1) IF the customer name slicer is connected to the Customer table THEN all the other report visuals update correctly and this specific example is broken.
2) IF the customer name slicer is connected to the custom DETACHED table you showed me THEN this specific example works but the connection to all other report visuals is broken.
Do you happen to have another way to do this that involves keeping the customer name slicer connected to the model?
Thanks again for your time.
Nathan
Super User
Here's a more robust solution using the pattern in this article:
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/
Create a calculated table (clone of Customers table):
CustomersVisual = Customers
Create an inactive relationship between Customers and CustomersVisual:
Create measure:
Total Amount =
CALCULATE (
SUM ( FactTable[Amount] ),
ALL ( Customers ),
VALUES ( Customers[FamilyID] ),
KEEPFILTERS ( CustomersVisual ),
USERELATIONSHIP ( CustomersVisual[ID], Customers[ID] )
)
The slicer should use Customers[Name]. This allows the other visuals to be filtered by the main Customers table. The table visual should use CustomersVisual columns (ID, Name).
Note what happens if you use Customers columns in the visual:
Let me know if this solution works for your report.
FactTable data:
- WinterMist
I’m very grateful to you.
No way I would have thought to try what you’ve given me.
Unfortunately, I still have not been able to get to a solution – even with all your help.
Route 1) I tried my best to replicate what you gave me, but (probably due to my ignorance) PBI does not allow it.
See attached PBIX 2022.03.21 Account Family – V1.
Route 2) I tried a different idea, but it only produces one good step forward. After that, not sure what to do.
See attached PBIX 2022.03.21 Account Family – V2.
NOTE: Actually, this forum will not allow me to share PBIX or ZIP files.
ROUTE 1 DETAILS
Your example shows the following relationship:
- CustomersVisual.ID to Customers.ID (1:*)
- Inactive
- Cross filter direction = Single
My attempt to create the same relationship between ‘Dim Customer’ & ‘Dim Customer Visual’ like yours, fails:
- PBI does not allow me to create a single filter direction relationship on ID.
- Error: “The filter direction you selected isn’t valid for this relationship.”
- PBI does not allow me to create a 1:* (many) relationship using the ID (CustomerKey). It only allows 1:1.
- Actually, I am confused how this could be 1:* since these tables should be identical and ID should be unique.
- I had to change the Cross filter direction to “Both”. Otherwise, PBI would not allow me to create an inactive relationship at all.
- NOTE: It is strange to me that you are joining to CustomersVisual on ID and not the FamilyID (RootParentKey), but I followed anyway.
I completed the rest of the items as you advised:
- Measure for Total Sales created
- The slicer is using ‘Dim Customer’[CustomerName]
- The table visual is using:
- ‘Dim Customer Visual’[ID]
- ‘Dim Customer Visual’[Name]
- ‘All Measures’[Total Sales]
ROUTE 1 RESULTS:
If no customer (or any customer) is selected, Total Sales is the same for all customers.
More importantly, the selected family of records is not isolated in the table. So…not good.
ROUTE 2 DETAILS
The key differences in this route are:
- The relationship between ‘Dim Customer’ & ‘Dim Customer Visual’ is on the FamilyID (RootParentKey).
- Doing this actually allows me to select “Single” for the cross filter direction.
- The relationship is *:* (many to many)
- This makes sense as most customers are part of a family, so the same FamilyID (RootParentKey) will appear multiple times in Dim Customer.
- Since Dim Customer Visual is an exact copy of Dim Customer, the same will be true for both tables.
- The relationship is active.
- At first, I tried making it inactive as you advised, but this does not return favorable results.
- Once the above relationship was created, the same Total Sales measure is now broken & throws an error:
- “USERELATIONSHIP function can only use the two columns references participating in relationship.”
ROUTE 2 RESULTS:
If no customer is selected, Sales is still the same for all customers. (Same problem as in Route 1)
However (here is the key difference), if any customer is selected, the family of records DOES appear correctly in the table!
For example, selecting Jennifer & John now returns the correct families of records! This is good.
- John is the root parent with the following 2 children: Adam & Abigail
- Susan is another root parent with the following 2 children: Ed & Jennifer
So this is big step towards the solution, but now I don’t know how to get the total sales for each Customer Key in this table.
It’s like I need to filter Fact S1.Sales by ‘Dim Customer Visual’.CustomerKey.
But I can’t do that in the model without creating ambiguity (multiple paths from Dim Customer to Fact S1).
So again, I’m stuck.
Thank you again for all your time.
https://drive.google.com/drive/folders/13HqZmd_S7YEcTLWUr2txNN-7ysRug3ZX?usp=sharing
- DataInsights
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:
- WinterMist
Thank you! Incredible. 🙂
- Wow. OK, I did it and it works.
- I clearly need an education. This is breaking my understanding of cardinality. I am completely confused now.
- ‘Dim Customer’ is a dimension table where CustomerKey is DISTINCT, right?
- In my mind, this means there is only 1 instance of each CustomerKey in the table, right?
- So multiple things:
- How would it ever enter anyone’s mind to [wrongly???] even try to label “1” as “many”?
- The entire data world hinges on NOT confusing these 2 very different ideas.
- 1 cannot be many.
- Many cannot be 1.
- Even if someone tried it [by accident???], how could it possibly work?
- For example, ‘Dim Customer Visual’[CustomerKey] value “[X]” can only join to ‘Dim Customer’.CustomerKey one single time.
- This is because there is only 1 record in both tables where CustomerKey =[X].
- So what does the “many” even mean?
- I feel like you’re messing with my mind 🙂 in that joke of a math problem where we prove that 1 = 0. I am doing what you are saying, and am glad it works. But I have no idea how.
Thanks again!
P.S. One step from success is still a long way from success if that step is believed to contradict logic.