From Excel data sources, need to create 5 different reports with different conditions

[Anonymous]

Hi guys

This is my first post in this community.

I was actually looking for some sort of design related guidelines for implementing my requirement with good effieciency.

I have 4 different excel data files from a third party source (TBL1,TBl2, TBL3 and TBL4)and I was asked to create 5 different reports with different conditions for each of the reports. All the tables can be joined by a Column which exists in all the tables.

Here some sample conditions

(Pull TBL1 Data -->If found in table TBL2.Clm1 and TBL2.CLM5=5

 Pull TBL2 Data --> If found in TBL3.CLM1 and TBL2.CLM1 and TBL4.CLM3=1).

Some of these reports may be using just 2 tables to genrate requested output and some of the reports are using 3 tables to generate requested output.

here are my questions:

1) Can we accomplish this requirements with single data model loading all 4 tables

2) Do we need to import all tables into data model and create a New Table with the requirements and use the new tables to populate the reports.

Please suggest a best way to accomplish above requirement.

[v-juanli-msft]

Hi @Anonymous 

1) Can we accomplish this requirements with single data model loading all 4 tables

No, you need to create relationships\create measures or transform the data model.

2) Do we need to import all tables into data model and create a New Table with the requirements and use the new tables to populate the reports.

you need to import all tables, you can either create a new table or create measures and display in a table visual.

For more details, please share some example data for me to test.

In addition, 

Pull TBL1 Data -->If found in table TBL2.Clm1 and TBL2.CLM5=5

 Pull TBL2 Data --> If found in TBL3.CLM1 and TBL2.CLM1 and TBL4.CLM3=1

Could you show an example to illustrate?

Are the values in a column which all the tables can be joined by distinct or repeated?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I have accomplished the requirement by importing all 5 files into PowerBI and started building new tables using merged queries where i have applied all required left join (not in) conditions to filter out unwanted data.