Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to iterate over table names within a loop

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!!

 

 

 

1 ACCEPTED SOLUTION
danextian
Super User
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.

 

danextian_1-1678244994089.png

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you, @danextian! I will try the approach you suggested

danextian
Super User
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.

 

danextian_1-1678244994089.png

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.