Left outer join - matrix visual

[Jinsoo]

Hello Power BI community,

I'm struggling with a situation with the matrix visual. 

Please find below an image with the data model and some matrix situations to illustrate what I'm facing with and what is expected. 

So to sum it up :

• Situation A : Matrix shows good results (level 1 + level 2 Amount_B) but I miss ID_level 3 in matrix rows. 
• Situation B : when I add ID_level_3, the row identified by ID_level_2 (name C) disappeared from the  matrix due to the fact that there is no ID_level_2 (name C) in FACT_A and there is an inner join between FACT_A and DIM_LEVEL_3
• The expected result is to have a matrix with levels 1 + 2 + 3 as rows and all amounts_B

What I tried but is unsuccessful :

• Check the "show items with no data"
• Create a new table in Power query based on FACT_A and appended with DIM_LEVEL_2 to get all the ID_LEVEL_2 and ID_LEVEL_3 values.This table is then linked with DIM_LEVEL_2 and DIM_LEVEL_3. When I add ID_LEVEL_3 in the Matrix, it shows good total Amount_B on level 1 and 2 but I've got all ID_LEVEL_3 for each ID_LEVEL_2 and repeated amount_B
• I tried to use NATURALLEFTOUTERJOIN function between FACT_A and FACT_B but it's not working. Not even sure if it's the good way to do it.

Thanks for your help. 

BR

Jinsoo

 

[vanessafvg]

are you able to share your file?  this probably has to do with how your data has been modelled. why do you not create one dimension with all your levels in on table?  this seems like quite a convoluted design

[Jinsoo]

Hello, 

Unfortunately I'm not able to share the PBIX file.

Regarding your suggestion, I've got some questions. Let's say I create a new table (e.g. : DIM_ALL_LEVELS).

• Will the data model look like this? 
• As I don't have a native hierarchy levels, I can't built it from DIM_LEVEL_1, DIM_LEVEL_2 and DIM_LEVEL_3. So it would mean I should built it from FACT_A which contains the 3 levels?
• Considering I could built this DIM_ALL_LEVELS table, don't you think I'm going to run into a many-to-many relationship problem?

Thanks. 

Jinsoo

[vanessafvg]

I think in order to truly understand what you are trying to do and why i really need an understanding of the business requirement here.  what are level 1 - 3 based on?   if you put them all into one table, why do you see there being a many to many relatioship issue?  its hard to know without being able to see the data and undertstand what is the goal.  Hope that makes sense.  Also in order to group these levels together, one needs to understand the context of them