Creating a measure/column that is used to apply a filter

[price1d]

Hello,

I have a dataset based on a fact table with several dimensions.  The business users would like the ability to make a selection that in turn applies a filter.  I have tried to implement this without success and so I am hopeful that someone can provide guidance.  Ideally the user experience would be as follows:

1. User adds column (measure) "product/seller filter" to filter pane that has two options "product" and "seller"
2. If user selects "product" then a filter 'product'[masterproduct] = "N" filter is applied
3. If user selects "company" then a filter 'measure'[mastercompany] = "N" filter is applied

The measure and product tables are related to each other by a 1:M relationship.  Ideally "product/seller filter" would be on the measure table but it could be in its own table if necessary.

Thanks in advance

[price1d · Accepted solution]

@AlB thanks but you're correct - applying the construct to all measures is not practical.  It does not appear that a solution is readily possible in Power BI.

I have instead developed a hybrid solution.  In the datasource (SQL Server) I have built a view that joins the measure and product tables and then left joins this to an inline table.  The inline table has the two userselection values and the left join uses an OR condition that applies the logic I require.  The view returns the measure tables unique key and the userselection values.  

I have imported the view into power query and then created a relationship between it and the measure table.  The relationship is set to a bidirecitonal filter.  

As such, the userselection shows both options and selecting one or the other applies the filter as required.

[AlB]

Hi @price1d 

I'm not sure I understand but try this

1. Create a slicer table SlicerT with two columns. You can then place SlicerT[UserSelection] in a slicer so that the user can choose

Userselection	Master_P-C
Product	N
Seller	N

2. Create 1-to-many relationships between SlicerT[Master_P-C] and  'product'[masterproduct], 'measure'[mastercompany]

That would implement the filtering. I'm guessing you'll need some more operations but you don't specify anything else.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[price1d]

Hi @AlB 

Broadly speaking you've modelled the approach I'm after but it must filter product or seller not both.  To expand: in your suggestion, you state that there will be two relationships created:

SlicerT[Master_P-C] and  'product'[masterproduct], 'measure'[mastercompany]

The issue with this configuration is that the user will select one value from 'userselection' but that will filter both relationships.  The selection must filter only 1 relationship.  

For example, user selects 'userselection' value of "product" then via the relationship to 'product'[masterproduct]  this is filtered to "N".  The relationship to 'measure'[mastercompany] must not be filtered.

Hopefully that makes the requirement a little clearer? Thanks! 

[AlB]

@price1d 

Is it an option to apply the filtering on the measures you have? Either by

1. Using USERELATIONSHIP to activate the relationships I described earlier depending on the content of the slicer

2. Eliminating the relationships  but applying the filter (="N") in the measures through a CALCULATE

In both cases you'd have to apply the construct to all your measures where you need that behaviour, which would be tedious. It could potentially be simplified though by applying calculation groups

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers