Common filters for measures from different facts

[Rohita]

Hi Experts,

I am trying to understand whether there is a way to display measures from two different facts on a same dashboard with common filters. I have fact 1 with 5 dim keys and 2 measures and fact 2 (aggregated) with same 5 dim keys and 3 measures. I would like to display the visualization of the 5 measure (saperately thought) but on the same page and utiize the same set of filters (5 dims) to view the data.

Creating a single direction relatioship with the dimensions is one way, but that limits the interactivity of the data in the dimensions For Ex -  If I select a facility, I also want that the list of department shrinks to that facility. In addtion the 5 measures display the value related to selected facility and department. Is this viable in power BI. I know that Qlikview does that making synthetic keys. Looking for any expert advice.

[Anonymous]

Hello @Rohita

The Power BI Engine is different the Qlikview, and the Slicer doesn't work dynamicaly between each other. In other terms and according to your example, if you choose a faciliy in on slicer, the slicer showing the departement is not updated to display only related member (just it could appear in Excel for example).

But I think there are workaround to do that :

The first one is to build a "hierarchy" in your visual (Histogram or pie chart), putting the Facility on the top level, an going down with the departement. Thank to this, you can drill from your facility to the level you want, with only related members.

The other solutio, could be to create 4 visuals and 1 slicer. The slicer is for the facility, the others visuals (histogram for example) are for the others 4 dimensions you've got. In manage Interactivity between visual, you change it from Highlight to filter (I guess you have to do it for all your visuals), and I think that you should see only relevant member each time so select a facility and one member on a histogram.

But remember that you still cannot do the same thing as Qlikview, i-e, keeping selected member on each visual.

Good luck

Sebastien

[mike_honey]

I would use Edit Queries to Append the 2 fact tables together into one table.  The data from the 5 dimension keys should present as 5 columns, which you can join to your dimensions in the Relationships view.  The data from the 5 measures will appear in 5 columns - some rows will show null for 2 measures, others will show null for 3 measures.

At present the interactive filtering will only work as a report page or dashboard Live Tile.

[Rohita]

Thanks @mike_honey. I am curious as to how the append would work especially when the two facts are at different granularity levels. Let me confirm that the fact 2 is not just the aggregate of fact 1. Fact 2 is rather an aggrgation of other sets of measures for the same dimensions as in fact 1. Looking forward to more insights. 

[mike_honey]

Hi @Rohita .

I think I already explained above how the actual Append step would work.  I presume you are worried about the aggregation results?

From the aggregation perspective, the granularity of the fact tables wont matter as the set of dimensions is the same - the aggregation will roll up both measures for the selected dimension attributes. 

I'm assuming the measures are the typical Aggregation of a source column e.g. Sum, Min, Max.

I would just take a copy of your PBIX file and give it a try on a subset of data.

[austinsense · Accepted solution]

Please don't append two fact tables that have different granularities - that's a major no-no.

I think @Phil_Seamark had the right idea of connecting the fact tables to a common dimension.  You would want to use the bi-directional relationships between your fact tables and all your dimensions.  That will let you set up the slicers to cross-filter each other based on what's in another slicer - even if one dimension has to reach through the fact table across to another dimension.

The short answer to your question is all of this is possible in Power BI and it's exactly what the tool is designed to do.

[Phil_Seamark]

How big is your data model? If it's less than 250MB you could build the model in Power Pivot and connect both fact tables to a common dimension table where you can set the filter.