Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am struggling to filter a table by selecting all rows which have column values associated with the selected slicer values.
See example below.
Table = “Customers”
Slicer = “Name”
DESIRED OUTCOME:
Regards,
Nathan
Solved! Go to Solution.
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.
Proud to be a 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:
Proud to be a Super User!
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:
Proud to be a Super User!
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.
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).
Proud to be a Super User!
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.
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:
Proud to be a Super User!
Amazing! You're a Power BI wizard!
3 tables for 1 dimension. Never would have thought of that.
- I was able to use your solution right away in the fake-data PBIX in this thread.
- However, it took me a while to make it work in the real-data PBIX our company uses.
- Had to overcome a couple errors:
1) Join Paths are expected to Form a Tree...However, 2 join paths were being used.
2) USERELATIONSHIP function has to use the fields defined in the model.
Finally, I was able to get it to work, thanks to you!
My sincere thanks to you for a MASSIVE amount of help on this project!
Nathan
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
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:
Proud to be a Super User!
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:
My attempt to create the same relationship between ‘Dim Customer’ & ‘Dim Customer Visual’ like yours, fails:
I completed the rest of the items as you advised:
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:
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.
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
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:
Proud to be a Super User!
Thank you! Incredible. 🙂
Thanks again!
P.S. One step from success is still a long way from success if that step is believed to contradict logic.
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".
Proud to be a Super User!
Thanks again for all the help! Definitely would not have figured this out without you.
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.
Proud to be a Super User!
Follow up question to this. It works great so thank you for that!
However, If I am trying to then add a calculated value, how can I do it so that when the value is put into the table, it shows only corresponding values instead of every single person
User | Count |
---|---|
89 | |
88 | |
75 | |
67 | |
58 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |