cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SANTEK
Helper II
Helper II

Filter depending on another filter

Hi everyone,

 

How can I have a filter which is dependant from another filter ?

 

Example : 

FIlter 1 = Companies

Filter 2 = Activities

 

If I choose one company in Filter 1, I would like to have only activities from the selected company.

 

Is it possible? How to do so?

 

Have a nice day.

1 ACCEPTED SOLUTION

The problem you are facing is that Filter 1 propagates to clients and the invoices but stops there. It is not filtering activity because the there is no filter direction from Invoice to Activity. Are companies directly related to activities? If so, then you need to remodel the data. If not, then it is going to be difficult helping you without understanding the reports you need to develop. I can suggest a solution but it might ruin other reports for you. You need to be aware of the impact of this change before doing it. One solution is to change the filter direction of the relationship between Invoice and Activity to "Both". But again, as I told you, this might ruin other reports for you. 

Another solution you might consider is to include the field of the Activity table you are using as a filter in the "Invoice" table through a Related() or Lookup() function. Then you can use the field from the Invoice activity for Filter2.

View solution in original post

10 REPLIES 10
PaulDBrown
Super User II
Super User II

@SANTEK 

Here is a simple way. First the model in this example:

model.JPG

 

Create a measure (the table "Sales" is your fact table) :

 

Filter Other Slicer = COUNTROWS(RELATEDTABLE(Sales))

 

Add this measure to the slicer's filters (you want to select the corresponding values) in the filter pane and set the value to greater or equal to 1:

Result.JPG

 

And this is the result. The selection in the "Channel" slicer filters the "Item" slicer :

Filter other slicer.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I love the solution, but I don't understand how it works.

I love the solution but i do not understand how it works. 

 

 

@SANTEK 

The way this solution works is basically creating a measure to identify the rows being filtered by slicer 1.
As per Microsoft's documentation:

"The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify".

When you wirte the expression RELATEDTABLE(Fact Table), it is creating a table of rows of the fact table which is in a relationship with the slicer. So if you filter value "A" in the slicer, it will return a table containg all the rows which have value "A". By wrapping it in COUNTROWS, you get a measure returning a value of 1 for each of these rows.

You can then use this measure as a filter for the second slicer, and since the RELATEDTABLE changes the context in which the data is filtered, the slicer values are filtered to show only the filtered rows from the fact table containing the equivalent values for the slicer. So the fact table, filtered by slicer 1,  is now "filtering" (using COUNTROWS) slicer 2. Make sense?

BTW, you can use this measure to filter either slicer based on the selection made in one. So in my example, if I add this measure to both slicers, the selection on Channel slicer will filter the values in Item slicer; a selection in the item slicer will filter the values in Channel slicer.

 

If you need help to replicate this in your model, please provide a depiction of the model (the relevant dimension tables & the fact table they are related to)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






SANTEK
Helper II
Helper II

thanks @gdarakji 

gdarakji
Resolver III
Resolver III

Hi @SANTEK ,

By default, the filters will filter each other. So the behavior you are looking for should be there by default. If it is not working, then it must be one of 2:

1- The interactions of Filter 1 are not set properly. For that, you can click on Filter 1 -> Format -> Edit Interactions -> make sure it filters the second filter.

2- The data model doesn't have correct relationships between the tables containing Companies and Activities. For that, you need to create a relationship in the model and make sure it has the correct direction.

I tried to make a relation between the 2 tables BUT it is a little bit more complicated.

 

PowerBI let me know that it is "already connected" by another relation.

 

In fact :

 

Company -> Client -> Invoice <- Activity

 

How can I do better?

The problem you are facing is that Filter 1 propagates to clients and the invoices but stops there. It is not filtering activity because the there is no filter direction from Invoice to Activity. Are companies directly related to activities? If so, then you need to remodel the data. If not, then it is going to be difficult helping you without understanding the reports you need to develop. I can suggest a solution but it might ruin other reports for you. You need to be aware of the impact of this change before doing it. One solution is to change the filter direction of the relationship between Invoice and Activity to "Both". But again, as I told you, this might ruin other reports for you. 

Another solution you might consider is to include the field of the Activity table you are using as a filter in the "Invoice" table through a Related() or Lookup() function. Then you can use the field from the Invoice activity for Filter2.

View solution in original post

And what do you think of adding a merging request by ID and just take one field of the activity table ? If I understand you well, it's like a lookup function.

 

Can this be a problem for the future of my report ? 

 

Thanks for all your answers by the way.

The merging by ID between the Invoice and the Activity table will be the best solution to achieve your requirement. It will not cause any issues. It is similar to using the Lookup function solution I proposed except that it will not produce a redundant field. The only drawback to this is that the model size will increase which might impact performance a bit. But if your data is not large, then it will not have any impact.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors