weird behaviour (cross join?) in very simple model - table visualization

[shoemaker27]

Hi!, Im getting very weird results, I have this simple model:

I have this table visual using sum of reseller sales as an implicit measure (I want to avoid it):

The results are OK, but If I add the measure fact2 reseller = sum(fact2[reseller_sales]) to the visual Im getting like a cross join:

What Im doing wrong? is there anything I can add to the measure to avoid this?

thanks  a lot!

[JohnShepherdAPD]

Hi I think this is because you are using the Fact 1 ID in the visual that has no relationship to ID in Fact 2

if you use the ID from Fact 2 you shouldn't see this behavior

you can see this behavior is also present when you use the implicit column sum as 3000 reseller sales is actually also duplicated, assuming reseller_sales = 3000 for Bike and not 6000 as per your image. 

[shoemaker27]

hi, yes I know reseller_sales are 3000 for bike, and is just what I need, I want the reseller sales for each row, visually is duplicated, but at total level is Ok, I need that to be able to do other calculations at the fact1 level. The current model is just an example of the real one.

[Jog_ch_um]

The data is not entirely clear to me, but your weird results are due to the id column in fact1. 

You'll see that the result of the sum of fact2 is repeated for each id from fact1. To solve this you could simply remove the id column from your table, but I'm not sure what the id in fact1 is meant to represent. 

If you were to remove the id column you would get the sum of both fact1 and fact2 by category. 

[shoemaker27]

Hi Jog, removing id from the visual avoids the behaviour, but I need the table to show the data at the lowest level of detail (fact1).

My question here is why if I drag the column "reseller_sales" (making an implicit sum measure) to the visual works perfect and If I I use the measure fact2_reseller =sum(fact2[reseller_sales]) does not work?

thanks!

[Jog_ch_um]

Hey Shoemaker, 

Alright, gotcha. 

First I would like to pose the question if your results are really 'correct' with an implicit measure. 😉 

Not saying the results are 'wrong' per se, but at the very least confusing for an end user. Even if your users are okay with it now.. will new users be okay with it in the future? Or the same users with bad memory in the future. 😉 Combining two fact tables of different granularities in one model is generally a bad idea/practice. It'll set you up for trouble. 

As for the reason the implicit measure 'works' and explicit doesn't. I'm not 100% sure, because I'm not entirely positive how an implicit measure gets calculated. But I suspect it has something to do with context transition. The explicit measure below returns the result you'd expect without any bidirectional relationships and other shenanigans. 

[JohnShepherdAPD]

You could either

1) add filter to fact2 reseller measure NOT(ISBLANK(Fact1 amount))

2) add a visual filter to fact1 reseller is not blank

3) add bidirectional relationships

[shoemaker27]

thanks John!, 

The NOT(ISBLANK(Fact1 amount)) did not work.

The visual filter works.

The bidirectional filter also works.

my question is, why using the implicit measure works and not with my defined measure?

Following the "best practices" suppose to avoid implicit measures, and also the use of bidirectional filters.

thanks!