Create a report with 3 columns - 2 tables have many to 1 relationship with 1 other table

[Anonymous]

Hi everyone,

I try to create a report with 3 columns:

1. Case Key from Case Table

2. Status from StatusHistoty table; and

3. Progress from ProgressHistory table

The relationship as pic.

Since there is no relationship between StatusHistoty and ProgressHistory, it shows error.

Any help would be appreciated.

Thanks

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

I have built a data sample:

Based on my test, you could change the relationships to Many-Many , and then create visuals:

If it's not your expected, you may try to merge these three tables in Power Query.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gaurav_Lakhotia]

Hi @Anonymous, try with BI-Directional relationship.

Change cross filter direction to both for both the relationships.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[Anonymous]

Hi @Gaurav_Lakhotia 

I tried that, either one or both relationships, did not work.

Thanks

[amitchandak]

@Anonymous , You can not take un summarized columns from both tables on many side.  You can take un summarized column from dimension, Fact 1 and from Fact2 you can have only aggregated columns

[Anonymous]

Hi @amitchandak 

So you mean there are no way to get them into 1 table?

Thanks!

[amitchandak]

@Anonymous , Unsummarized from both facts( aggregated you can have from both) is not possible.  You can have aggregation on one of the tables (on many side) and use

[Anonymous]

Thank @amitchandak