Multiple Fact Tables Issue when you are attempting to use more than 1 non-conformed dimension

[dnauflett]

I have a very interesting reporting pattern that MicroStrategy handles seamlessly and we were expecting PowerBI to handle it just as easy.  In our Warehouse we have mulitple fact tables with conformed dimensions (Where possible). We have a few dimensions that are specific to a individual Fact Table. If we are using only the conformed dimensions, PowerBI can handle as many Fact Tables as we want to throw on the report and it even resolves any grain differences and presents the proper metrics.  PowerBI can also handle place 1 of the non-conformed dimensions on the report without issue.  If we are ONLY using Non-conformed dimensions from 1 for the tables you can have mulitple Non-conformed dims as long as they are from the same Fact Table. As soon as we throw on a Non-conformed dimension from another Fact Table, it produces a error complaining about a missing relationship (was not exepecting it to do that).  It appears that it wants a relationship between the two non conforming dim tables, which there is none. 

MicroStrategy, which we are trying to migrate from to PowerBI handles that scenario effortlessly.  Since we have hundreds of reports that do this, its hindering our migration effort because we have not figured out a work around.

Simplified Model.  The fact tables are in the middle.  The conformed dims are on the left and the non-conformed dims are on right.

We have measures from all 3 Fact tables and everything renders just fine until we drop in BorrowerAssetTypeCode. It goes south and produces the relationship error.  I know it wants the relationship between the two Dims, if I change BorroweAssetType Code to a measure by telling it to take the first or last it will render the report, but we then loose all the other values.

This is the desired output.  MicroStrategy can do these types of reports all day long and twice on Sunday.  Since there is not a relationship between the two highlighted Code tables/Fields (Gold and Yellow), its doing a controled cartisian between LoanDispostionTypeCode and BorrowerAssetTypeCode. This is precisely what we want.  For each DispositionTypeCode for a given account the Assets will be repeated.   It must be a way to get PowerBI to render this?

[dnauflett]

Hey @Data-estDog 

I'm suspecting that I maybe using UseRelationship wrong.  The error goes away when I use explict measures, so check on that box. I also have a work around in my bigger model to get rid of the cartisian rows, but dont really like my solution.  I'm still trying to understand why Powerbi wants to do the cartisian thing on and Dim that is not part of the fact table providing the measure.   I uploaded @Greg_Deckler model to my google drive. It has the modification where I added more values to the non conformed 2 Dim that are not being referenced in Fact2.

https://drive.google.com/file/d/1ctzGmU5MkjXVHdPbr1W_W2YTG0RqRkP8/view?usp=share_link

[dnauflett]

I added my work around to the model. I basically added a RecordCount field and set it to 1 then added a filter to remove any record where that new field is blank.  Solution just feel kluggy and not efficent because its spanning on those rows under the covers and I'm just removing them at the end. We have some Dims that have quite a few records.

https://drive.google.com/file/d/1ctzGmU5MkjXVHdPbr1W_W2YTG0RqRkP8/view?usp=share_link

[Data-estDog]

The problem is not conformed vs non-conformed dimensions. It is the case I believe, that Power BI does not know which releationship to use with the selected columns. 

You can try to implement USERELATIONSHIP in each of the applied Measures. I know this is annoying but it works.... see here: 

[Greg_Deckler]

@dnauflett I'm having difficulty replicating this behavior. See attached PBIX file. The table visual has both conformed and nonconformed dimensions in it and values from multiple fact tables. Perhaps you can provide a sample that recreates your issue.

[dnauflett]

@Greg_Deckler  Thanks for the quick response!  The model you have will work!  The problem comes when you have a 2nd non-conformed dimension on another Fact Table.  I updated your model to reflect this. When you drop the 2nd non conformed dim, it produces a relationship error.   I'm new to this forum and dont see how to upload it.

[Greg_Deckler]

@dnauflett OK, hmm, that was easier than anticipated, essentially just had to get rid of the implicit measures and make them explicit. See new PBIX attached below signature.

[dnauflett]

Good Morning @Greg_Deckler , 

Your solution got me almost there!!  I'm no longer getting the error when I use my real data, but now its doing a cartisian on all the values in the Non conformed dimension.  Basically repeating the value over ALL the records in the Dim table versus just the Dim values in use.  I was expecting it to put the results back together by joining on the common columns like our other tool does.

I recreated NonConformedDim2 and put in values that are not being used in the Fact2 table to show the problem.  

The only dim value being referenced is Medium, but for the report you can see that its creating a line for all the records in the Non Conformed Dim 2.   The desired results would be to only have the lines where Property5=Medium and not do that cartisian.  I can do some tricks where I filter out where Value2 is blank, but that can get very complicated with our real data set.  

[Data-estDog]

Please look at my reply from 12 Oct and the screen shots. You need to use USERELATIONSHIP and spell out the joins so the model knows which patht to take. 

[dnauflett]

Hey @Data-estDog ,  

First, it was very rude of me not to thank you for your suggestion.  Thanks for taking the time to help me!  I tried the Use Relationship on the new metric, but it did not seem to make a  difference. I still got the cartisian between the metric on the Fact Table and the non conformed dim that had a relationship with another Fact Table.  I tried every relationship in the model and it did not make a difference.  Maybe I'm using it wrong?  Let me take another stab at it.

[Greg_Deckler]

@dnauflett Yeah, unless you are designated a Super User, you don't have upload permissions. So people use OneDrive or Box and post a link. I updated the model and was able to replicate the behavior. I'm curious which tables it wants a relationship between. Regardless though, the fix for this should be similar to the disconnected table trick. Essentially, you form the relationship within the measures themselves and then you don't get the error. Let me see if I can create a fix for it but in the mean time, here is an updated PBIX with the additional dimension.

[dnauflett]

@Greg_Deckler , It wants a relationship between the two non-comformed dimensions.  We are very new to PowerBI, so I'm trying to figure out how to apply your recommendation.  Once again thank you, thank you and thanks you for the help!!