Hi Adele,

The dotted line indicates an inactive relationship. You can only have one active relationship between two tables. Even though you appear to only have one relationship between fContentMicroblog and dUser, you have many multi-directional relationships and these are allowing a relationship to travel from dUser to fContainerSpace to dUtcHalfHour to fContentMicroblog.

You need to change the relationship between dUser and fContainerSpace to a single direction and that should allow your relationship between fContentMicroblog and dUser to be active.

I talk about this in session 3.03 of the Power BI course. I recommend you watch this tutorial again as I suspect some of your other relationships should also be single direction.

Mynda

Thanks I'll watch again. 

AJB

Not sure if it's because it's Monday and the caffeine hasn't kicked in but I'm really having a hard time understanding the difference between cross filters. I've watched the tutorial a number of times and still don't get it.

Are there any hard and fast rules on when to use a single direction rather than bilateral?

Should all relationships between a fact table and a dim table have Cardinality of Many to One and a Cross filter direction of Single?

Related but not specifically a question about cross filtering: In the data model I attached above I have one set of microblog tables that I can pull how many likes, comments etc connected to 2 other tables that allow me to connect to the name of the particular microblog. It is joined to the dUtcHalfHour table so that I can see when the 'engagement' happened and joined to the dUser so that I can see who was engaging.

The other set of tables is a different channel that I want to report on and has nothing to do with the microblogs but has the dUser table and dUtcHalfHour tables as VLookup tables in common.

I want to report on each channel on a separate page of the report.

For even more fun there are 5 other sets of tables I want to bring in that also use dUser and dUtchHalfHour that would report on different channels.

I'm beginning to believe this isn't possible and I would need to build tables in PowerQuery but all of the tables would have to be in one excel file in order to import. None of the excel files currently have the tables in a worksheet they are all contained as OData connections and added to the data model as there are millions of rows of data in some of the fact tables. I've tried building all the the queries in PowerQuery/PowerPivot but the file is so bit it takes forever to load and refresh. Any ideas on how I would do this?

Hi Adele,

I think when you're a beginner with Power Pivot you should try to follow some simple rules:

Dim tables are the 'one' side of the relationship. The relationship between dim and fact should be single direction - arrow pointing from dim to fact. If you stick with those rules then you won't get yourself into any confusion or issues with the filters etc.

To answer your other question; you can connect Power Query to multiple OData feeds and then either merge that data into one fact table for the multiple channels, or keep the fact tables separate, although it would be more efficient to combine them. You don't need to download the OData data into one Excel workbook first, it can go direct from OData to Power BI via Power Query in Power BI. Of course if you tried to connect Excel Power Query to the OData feeds, and then bring the data into an Excel table, Excel would not cope.

Make sense?

Mynda

P.S. in future it's better to start a new thread for a different question.