Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
amrendra
Regular Visitor

Filtering based on subitems

Hello, I am trying to visualize JIRA work log using PowerBI. I have a table containing user, issue, logged hours, and date.

This table can contain 

  • A user can log hours for multiple issues
  • An issue can have multiple users logging their time.

I have a visual in the following form

 

User A

    Issue 1

    Issue 2

User B

    Issue 2

    Issue 4

 

and so on...

 

When I select  User A or User B - a filter (user is UserA/B or so) is triggered for other visuals on the page. (Works)

When I select Issues in the above visual - A filter ( Parent User + selected Issue) is triggered for other visuals on the page. 

 

I like to have a filter applied for the other visuals only for the select issue and not for (user + issue). How can I achieve this?

 

Thanks for your help.

 

1 ACCEPTED SOLUTION

@amrendra  Aha, I see your dilemna now. Because you want to cross-filter across visuals, you have a filter on User and Course. There's not really a way to clear the filter on User only from the visual you click while still keeping the User filter from the visual you want updating. So, you can copy the User table, you your example we'll call it Employee Associated, and then use that in the second visual. Now we can clear the filter from Employee without clearing the filter from Employee Associated with a DAX Measure:

 

Result Associated =

CALCULATE( SUM( Training[Result ]), ALL(Employee))

 

Relationships are very important here too: 

 

AllisonKennedy_0-1645694873597.png

 

Desired result:

 

AllisonKennedy_1-1645694899912.png

 

 

See attached pbix below signature.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

@amrendra You need a dimension table for issues, then you can isolate the filters for Issues only: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy ,  thank you for your reply. I do have dim for issues and authors.

 

It is similar to the example you shared and I used that data to create a quick example of what I am trying to do. I have two visuals on the page, one explaining the training data for users, while I have another visual related to the course view. I am looking at training data from user view and then decided to check who are all users also took that particular course.

 

I created a demo and exported the pbix file.
https://drive.google.com/uc?id=1eqowtCgZN7bme-DOyG3zy_wzDHbhuCCn&export=download

 

for example, I checked Mary and see that she has taken Excel Essential course, at that moment I would like to see who else has taken that course and right there clicked on the course. This triggers the filter for other visuals with (user + course) and it only shows me that for Mary.  While I wanted to quickly check who all also took that course, and expected (desired) a filter to be triggered only for that course instead of combination with its parent.

 

You mentioned isolating the filter but I am unsure how I can achieve that. I am sure I am missing something.

 

Thank you for your help.

@amrendra  Aha, I see your dilemna now. Because you want to cross-filter across visuals, you have a filter on User and Course. There's not really a way to clear the filter on User only from the visual you click while still keeping the User filter from the visual you want updating. So, you can copy the User table, you your example we'll call it Employee Associated, and then use that in the second visual. Now we can clear the filter from Employee without clearing the filter from Employee Associated with a DAX Measure:

 

Result Associated =

CALCULATE( SUM( Training[Result ]), ALL(Employee))

 

Relationships are very important here too: 

 

AllisonKennedy_0-1645694873597.png

 

Desired result:

 

AllisonKennedy_1-1645694899912.png

 

 

See attached pbix below signature.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.