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:
Super User
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:
Impactful Individual
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.
- DataInsights
It does seem counter-intuitive to define a relationship as 1:* when the data is actually 1:1. However, keep in mind that 1:* accepts data that is 1:1. There is no requirement that the many side of the relationship must have multiple rows for a value on the one side.
Bidirectional relationships can cause issues in a data model, so it's best to use them sparingly (if at all). In a 1:1 relationship, bidirectional is the only option. In a 1:* relationship, however, unidirectional is an option. Thus, we sometimes have to define relationships as 1:* in order to set the crossfilter direction to "single".
- WinterMist