March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a table with three columns from my data set and a fourth column that is a measure called relationships. The Measure is as follows: >>>Relationship = DISTINCTCOUNTNOBLANK('Aggregate Officer'[Unique identifier])<<<
Team Member | Name | Job Title | Relationships |
A | MICHAEL | VP AND CONTROLLER | 10 |
B | MICHAEL | VP AND CONTROLLER | 5 |
C | MICHAEL | VP AND CONTROLLER | 15 |
A | GARY | AVP | 25 |
B | GARY | AVP | 5 |
C | GARY | AVP | 10 |
B | Jim | Exec | 20 |
C | Jim | Exec | 10 |
B | Tony | Assistant | 30 |
C | Tony | Assistant | 35 |
I want the ability to slice the above table by team member but I only want the filter to impact the relationship column. So for example when I slice the below table by team member “A” all the names should remain on the table. The relationship column however would be blank for Jim and Tony because Team Member "A" does not have a relationship with these individuals
Name | Job Title | Relationships |
MICHAEL | VP AND CONTROLLER | 10 |
GARY | AVP | 25 |
Jim | Exec | |
Tony | Assistant |
Solved! Go to Solution.
Hi @dw700d ,
As far as I know, if you use [Team Member] column which is related to other columns in your visual, these columns like [Job Title]/[Name] will be filtered.
Here I suggest you to create an unrelated Dim Team Member table for the slicer.
Dim Team Member =
VALUES('Aggregate Officer'[Team Member])
Measure:
Relationship =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'Aggregate Officer'[Unique identifier] ),
FILTER (
'Aggregate Officer',
'Aggregate Officer'[Team Member] IN VALUES ( 'Dim Team Member'[Team Member] )
)
)
And turn on "Show items with no data", result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@olgad that does not work because the rows with blanks in the example above have no connection with Team Member A
Hi @dw700d ,
As far as I know, if you use [Team Member] column which is related to other columns in your visual, these columns like [Job Title]/[Name] will be filtered.
Here I suggest you to create an unrelated Dim Team Member table for the slicer.
Dim Team Member =
VALUES('Aggregate Officer'[Team Member])
Measure:
Relationship =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'Aggregate Officer'[Unique identifier] ),
FILTER (
'Aggregate Officer',
'Aggregate Officer'[Team Member] IN VALUES ( 'Dim Team Member'[Team Member] )
)
)
And turn on "Show items with no data", result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
82 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |