Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables that I am pulling from our database. One table is called utilityhistory and the other utilitymeters. I have posted the actual tables below:
utilityhistory
utilid meterid propid unit currread currdate prevread prevdate consumption adjustedconsumption amount postdate 142 154 146 001 395920 1/8/2016 0:00 391860 12/14/2015 0:00 4060 4060 24.28 2/1/2016 0:00 143 155 146 001 395920 1/8/2016 0:00 391860 12/14/2015 0:00 4060 4060 35.81 2/1/2016 0:00 144 156 146 001 395920 1/8/2016 0:00 391860 12/14/2015 0:00 4060 4060 1.6 2/1/2016 0:00 145 157 146 001 395920 1/8/2016 0:00 391860 12/14/2015 0:00 4060 4060 3.22 2/1/2016 0:00 142 154 146 002 529060 1/8/2016 0:00 525560 12/14/2015 0:00 3500 3500 20.93 2/1/2016 0:00 143 155 146 002 529060 1/8/2016 0:00 525560 12/14/2015 0:00 3500 3500 30.87 2/1/2016 0:00 144 156 146 002 529060 1/8/2016 0:00 525560 12/14/2015 0:00 3500 3500 1.38 2/1/2016 0:00 145 157 146 002 529060 1/8/2016 0:00 525560 12/14/2015 0:00 3500 3500 2.78 2/1/2016 0:00 142 154 146 003 1193330 1/8/2016 0:00 1192410 12/14/2015 0:00 920 920 5.5 2/1/2016 0:00 143 155 146 003 1193330 1/8/2016 0:00 1192410 12/14/2015 0:00 920 920 8.11 2/1/2016 0:00 144 156 146 003 1193330 1/8/2016 0:00 1192410 12/14/2015 0:00 920 920 0.36 2/1/2016 0:00 145 157 146 003 1193330 1/8/2016 0:00 1192410 12/14/2015 0:00 920 920 0.73 2/1/2016 0:00 142 154 146 004 1115440 1/8/2016 0:00 1112180 12/14/2015 0:00 3260 3260 19.49 2/1/2016 0:00 143 155 146 004 1115440 1/8/2016 0:00 1112180 12/14/2015 0:00 3260 3260 28.75 2/1/2016 0:00
utilitymeters
meterid name shortname graduated mincharge flatcharge 147 Ames Water AMES W 1 0 22.5 28 Belton Water & Sewer BEL-WS 1 0 25 150 Benton Sewer BENT S 1 0 15 151 Benton Surcharge BENTON 0 0 13 149 Benton Water BENT W 1 0 11.3 277 Berthoud Sewer BERTS 0 0 0 276 Berthoud Water BERTW 0 0 11.5 200 Bloomington GW Sewer BLMGWS 0 0 7.9 199 Bloomington GW Water BLMGWW 1 0 9.83 105 Canon City Sewer CANONS 0 0 20.92
I would like a new table that contains [utilid], [meterid], [propid], [postdate], [amount] from the first table (utilityhistory) and [flatcharge], [mincharge] from the second table (utilitymeters).
I think what I need to do is create a new table selecting the columns from utilityhistory that i want and then add two columns to my new table, create a relationship to utilitymeters using [meterid] and populate [flatcharge], [mincharge] using a DAX formula like: flatcharge = related('ruhor_views utilitymeters'[flatcharge]). I do not think I can do what I want in one step using a JOIN or UNION.
Any recommendations?
Thanks!
You can create a table that you want using DAX and the related function like you mentioned. It will perform better though if you create the table using the query editor or in the source database.
If you have a sql connection to both of the tables the easiest way would be to select just the columns you want from each table and then inner join the two tables.
If you don't have a sql connection then you can use the Query Editor's merge queries options. They give examples of merging data in this article (you have to scroll down a bit more for the part on merging). It is also pretty much the same thing as merging queries in Power Query for Excel.
Here is the basic flow you should follow to create a new table that contains columns from both tables:
Hi Twan,
What's the difference in level of performance in doing it in the query editor instead of using Dax? Is it really more performant?
I will do this using the Query Editor instead of DAX, I'll let you know how it goes. Thanks!
Hi,
Can you explain why exactly you want to put them in one table? I'll suggest to create a reliationship between both table and then you'll be able to use both data in any dashboards.
You can also create the relationship and then add the column from one of the two table depending on which direction you've created the relationship with the fonction RELATED.
If you want to absolutely create a new table, I'll suggest to create a new connexion on the table containing all the meterid value and only importing the meter id in order to create the relationship between the tables. THen use the RELATED function to get the column needed. Please keep in mind that it need to be 1 to 1 relationship in order to do this. In a case of an 1 to N relationship it's always the N side that'll have the RELATED function available.
Regards
Samuel Boulanger
The most honest answer of why I want to do this would probably be, because I want to improve my Power BI skills. The reason I started going down this path is because I need to use a line chart to display the billing breakout of all the single components that go into a residential water bill. The components of each bill depend on state and local regulations and differs depending on the region. In a power BI line chart I can only add one data source (from what I have seen), but if I have a single column called [amount] and the sum of amount is composed of multiple sources (service fee, surcharge, metered water,...) then Power BI will plot them on different lines. (see example image).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |