Creation of Customized report from Multiple columns from different tables

[Anonymous]

Hello,

I am trying to create a complex customized report from multiple columns from different tables. The expected outcome is to have the capability of selecting any column from any table and the data should display without any relationship errors.

For example: I tried to retrieve the following reports (only headers shown), but I am facing the relationship issues.

GB ID

City

Sub Event Date

AttendeID

Last Name

First Name

Function

SubEventID

GB ID

SubEven Date

Company Name

Bill code

empid

firstname

lastname

countryid

companyid

empid

firstname

lastname

eventid

Please click here for sample data and data model created by me. Please suggest, where I went wrong? 

Note: In actual scenario, the data quantity is very huge and complex.

Appreciate any suggestions or guidance in this regard.

Thank you

[v-juanli-msft · Accepted solution]

Hi

As tested for the first table, I find the error occurs when I add columns from table ‘Subevents’, this is because table ‘GBM’ and ‘Subevents’ have a many to many relationship and their relationship is built via the table ‘GB’. It doesn’t work when you directly use the columns from other tables. 

There are two workarounds temporarily to do this in Power BI

1. use the merge query function if you have messy data(heaps, two spreadsheets etc). That allows you to play with joining to sources of data with multiple columns.

    In query editor, select the query you want to see the joined data in. Click Merge Query (select the type of join you want(inner, outer, etc)) and then hold down control and select which columns you want to join from source and destination, it will show the count of hits it is making. Click okay and then select which columns you want to see in the original query.

2. Based on relationships built, you can also use DAX to use that column from a related table.

Best Regards

Maggie