cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
dw700d
Post Patron
Post Patron

Filter only Impacts one column

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 MICHAELVP AND CONTROLLER     5
C MICHAELVP AND CONTROLLER    15
A GARYAVP    25
B GARYAVP    5
C GARYAVP    10
B JimExec   20
C JimExec   10
B TonyAssistant   30
C TonyAssistant   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 
1 ACCEPTED 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.

vrzhoumsft_0-1685953712623.png

 

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.

 

View solution in original post

3 REPLIES 3
olgad
Super User
Super User

  • Hi, go to the name and click on the arrow down to pick show the items with no dataE8A6D898-1B12-4FDE-805B-CC821AA0A2FA.jpeg

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

@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.

vrzhoumsft_0-1685953712623.png

 

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.

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors