Combine totals from different sources in one table (keep them separately)

[MeloKutman]

Hi all,

I have a set of different SQL databases with data. Each database contains the record of visitors to a house per day, and I am looking at 7 different houses (databases).

*each database keeps the records in a different format and cannot be combined*  😞

I would like to calculate the totals visitors per house and then put all the totals in just one table where the records will be:

House 1 , #visitors

House 2, #visitors

…

My goal is to use a single bar chart for display.

I´ve been fighting with DAX for a couples of days, but I think the approaches I am using are not following PBI logic.

Any help?

thanks!

[Stachu · Accepted solution]

do you need to have live connection to the databases or can you import the data?
if you can import the data then  you can definaetely combine the separate databases in PowerQuery

Otherwise you could try with UNION in DAX

[MeloKutman]

Thanks @Stachu

---

I have tried UNION, and it does the job.

As a note: I have created a set of SUMMARIZE tables for each data source to calculate the values that I wanted to compare.

Then I have used UNION to combine all the tables. Tricky part: you need to make sure that the columns are in the same order in each of the tables in order for UNION to work fine.

@MFelix, thanks also for your help. Yes, I haven't provide with a detailed data.. in fact I simplified my initial post because the data formats and sources are very very different, and I was more after the approach than to the real solution.

Thanks!!

[MeloKutman]

Thanks for your reply.

I need live connection to the databases. 

So, in order to use UNION, should I need to create new tables where I calculate the totals of each database and then use UNION to merge them all together?

It sounds good. Thanks

[Stachu]

you can also use table expressions to get them into format you want
https://msdn.microsoft.com/en-us/query-bi/dax/union-function-dax

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

[MeloKutman]

Hi all,

I have a set of different SQL databases with data. Each database contains the record of visitors to a house per day, and I am looking at 7 different houses (databases).

*each database keeps the records in a different format and cannot be combined*  😞

I would like to calculate the totals visitors per house and then put all the totals in just one table where the records will be:

House 1 , #visitors

House 2, #visitors

…

My goal is to use a single bar chart for display.

I´ve been fighting with DAX for a couples of days, but I think the approaches I am using are not following PBI logic.

Any help?

thanks!

[MFelix]

Hi @MeloKutman,

First of all your setup is not completly perceptible, if you could add some example of the strutures of the several tables and some sample data would be easier to help you.

However looking at your question if you have some common information (date, ID, ...) something on your table that allows you to make a Dimension table to have the information per group you could then add some measures/calculated columns.

In thew attach PBIX you can find an option I have a column in each table that identify the house then I created a dimension table with all the house names then made an additional columns using the following syntax:

NumberVisits =
SWITCH (
    DimHouses[House];
    "House1"; COUNT ( House1[ID] );
    "House2"; COUNT ( House2[ID] );
    "House3"; COUNT ( House3[ID] )
)

This returns the number of visits:

This is just an example of what can be done but as said without any full details about your setup is dificult to help you any further.

Regards,

MFelix