Power BI - Merging multiple customer tables

1
votes

enter image description here

Connection Type: Direct Query to multiple sources so limited DAX available especially in Power Query load.

Data Model Query: The Data model is not a perfect star schema but there is an attempt to separate tables into business processes and lookup tables. There are probably a few issues to discuss the current data model. I only have 1 question at this time.

My current goal is to generate a single summarised customer table to replace the current two tables that have some measure I need like the number of app customers, a number of total customers, date customer first accessed app etc.

So I cannot merge the 2 customer tables and add calculated columns and measures at the import stage as power bi does not support or allow it and sql is out as I am using direct query. My plan is to create a summarised customer table using DAX summarise function on front end visual page, that has only the app customers and then measures like the total number of customers etc. Is this best practice or is there a better way of approaching this? Understand you would ideally do in sql, or power query but in these circumstances, I think this is the best way but wanted a second view.

1
powerbidaxdata-modeling
What you are trying to achieve looks quite simple from your description. However, it will be helpful if you provide sample data for all tables and expected summarized output. – Pratik Bhavsar
Cant share sample data due to GDPR. However, I just want to validate I am using the right approach when you have multiple customer tables in your data model. Some data modelling insight is what I am looking for in this specific situation. Customer App table is just a list of app customers and Customer table is a big list of all customers with all the normal customer specific info like age etc. – James Khan
@JamesKhan when anyone requests sample data, usually they want dummy data that looks similar to the real thing. A few rows per table to simulate the foreign keys usually is enough. – mxix
Regarding the model 2 tables should be fine. But you could just merge them (left join) into the customer table (non-app one) in power query for example. – mxix
Thanks @mxix. I was thinking about this and all I need is to stick a calculated column in the customer table to indicate whether it is an app customer or not and then hide the customer app table as it is just a list of customer id and other columns give no value. – James Khan

1 Answers

1
votes

Is there a reason to use Direct Query over Import? If you are in Import mode, you can easily Append the two client tables together in PowerQuery.

Treb Gatte, Power BI MVP