Filter different visuals on same selected value but from different dimensions

[KeithDunn]

I am developing Power BI dashboards from a tabular cube based around revenue measures.  Each revenue item is associated with multiple dimensions and some of these dimensions contain a Region value. For example, imagine that there are Customer, Reseller and Manager dimensions and each one has a Region as one of its attributes.

In this example, the Regional Manager would like a high level dashboard that contains a single Region selection and three visuals, one with measures for all the Customers in the selected region, one with measures for all the Resellers in the selected region and one with measures for all the Managers in the selected region. 

I cannot figure out how to filter all three visuals by a single Region selection when each of the regions are different dimension attributes. Note that Region is a coded attribute with a small number of valid values (it is not free-form). We could have put Region in a shared sub-dimension but decided that this was overkill for this cube. However, we can adjust to that approach if needed to address this challenge. But I would still need help understanding how this would work using a shared sub-dimension.

This seems like a common question but in this case I have searched and cannot find a comparable posting. Thank you for any advice you can give.

[KeithDunn · Accepted solution]

Here is the solution that worked for me:

1. Create a separate lookup dimension that contains all the values for Region. In my case there was a dimension table available in the data warehouse.

2. Create inactive relationships between the RegionCode in the lookup dimension and the RegionCode values in the Reseller, Customer and Manager dimensions.

3. Add a slicer based on the Region lookup dimension to the report. Note that the relationships above need to be inactive because if they are active relationships, the lookup slicer will filter all three dimensions at once where we actually want to filter only one of the three for each measure.

4. Create custom measures that use the USERELATIONSHIP filter to filter the measure by only one of the three relationships, leaving the other two inactive.

5. Use these measures in the report. Generally this type of report would be high level report showing just a few important cards or KPIs based on these measures. In my case this report will primarily be a portal-type report that provides links to separate reports that show details for measures related to Managers, Customers or Resellers for this region. 

[v-xiaosun-msft]

Hi @KeithDunn ,

Could you please tell me the relationships between four dimensions or provide us some sample data?

And you can reference the following ducument which is about using one dimension to filter another dimension.

One Dimension Filters Another Dimension in Power BI - RADACAD

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KeithDunn]

Thank you @v-xiaosun-msft for your response. Here is a visual of the star schema described above. It includes only the elements needed to illustrate the question. 

This is a typical data warehouse layout where there is a one to many relationship from each dimension table to the fact table. As a reminder, in our actual application this data warehouse has been used to build a tabular AS database, which is the data source for the Power BI report. In the real database there are other dimensions, including a Date dimension that could also separately filter the data. They are omitted here for simplicity.

Here is a limited set of sample data for the schema above:

From our knowledge of the source applications, we know that the list of region is a coded value and has a small number of valid values: North, South, East and West.

The use case is that the end user must be able to make a region selection from a single list and see three Table visualizations, one showing total RevenueAmt by Customer for the matching Customer Region, one showing total RevenueAmt by Reseller for the matching Reseller Region and one showing total RevenueAmt by Manager for the matching Manager Region.

For example, selecting South will give a Customer table showing Customer Two and a total revenue of $500, a Reseller table showing Reseller One with revenue of $500 and Reseller Three with revenue of $250 for a total of $750 and a Manager table with Manager Three and a total revenue of $500.

The real database has more fields and uses industry-specific terminology but this is an almost exact representation of the actual use case.    

Using slicers, selecting "South" would require three selections since there must a separate slicer for each of the dimensions. This would be fine if the use case demanded this flexibility (and other use cases might). But this use case requires exactly one visible slicer to filter different dimensions in different visuals.