Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working in Power BI with customer survey data that is set up in a less than ideal way. Instead of adding each new survey's information to an overall table, the database was set up with each survey as a separate table. No way around that at this point, unfortunately.
For the most part, the name of each of the 100+ tables is equivalent to the survey name, but that name doesn't appear within a survey table columns.
ABC_program_2019_q1
ABC_program_2019_q2
ABC_program_2019_q3
ABC_program_2019_q4
XYZ_program_2019_q1
XYZ_program_2019_q2
XYZ_program_2019_q3
XYZ_program_2019_q4
There is another table that I need to use: 'Participants', which includes a column with the survey name (so basically the equivalent to the name of each survey table). It also has the total # of participants [participants], which is needed to calculate a weighted average
My task is to create some calculated measures that aggregate information from each survey table. So far, the approach has been to do this by manually replacing the table/survey name (in bold), as in the following:
Revenue_Baseline = (AVERAGE('ABC_program_2019_q1'[salesrev_avgrevenue_monthly_baseline]) /1) * LOOKUPVALUE('Participants'[participants],'Participants'[survey_name], "ABC_program_2019_q1") )
+
(AVERAGE('ABC_program_2019_q2'[salesrev_avgrevenue_monthly_baseline]) /1) * LOOKUPVALUE('Participants'[participants],'Participants'[survey_name], "ABC_program_2019_q2"))
+ etc. etc. etc.
Is there a way to write a loop that can iterate over the values of 'Participants'[survey_name] to:
1) fill in the survey table name (so that I can get to the column with revenue information) and
2) get to the relevant number of participants
Many thanks!!
Solved! Go to Solution.
Hi @Anonymous ,
You can't loop tables in DAX. You'll have to specify each table when you create a measure/calculated column/measure. Use Power Query to combine relevant tables into one. 3
When you connect to a database, say Access, the navigation prompt normally lets you choose which table to connect to. If you move a few steps back you will see all available tables in that database. You can opt to expand the data column/the column which contains the data of each table instead of accessing just one specific table. Of course, you need to filter out all the other uneeded tables first before expanding. This will create a single table of all tables in that database that meet your criteria. Note:This approach can be slow (as this is similar to looping) depending on the data source size/server speed, etc.
Proud to be a Super User!
Hi @Anonymous ,
You can't loop tables in DAX. You'll have to specify each table when you create a measure/calculated column/measure. Use Power Query to combine relevant tables into one. 3
When you connect to a database, say Access, the navigation prompt normally lets you choose which table to connect to. If you move a few steps back you will see all available tables in that database. You can opt to expand the data column/the column which contains the data of each table instead of accessing just one specific table. Of course, you need to filter out all the other uneeded tables first before expanding. This will create a single table of all tables in that database that meet your criteria. Note:This approach can be slow (as this is similar to looping) depending on the data source size/server speed, etc.
Proud to be a Super User!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |