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

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.

Reply
spencerhoyle10
Frequent Visitor

DAX macro / dynamically switch table to be used in measures

Hi, I am trying to dynamically switch the tables used for specific DAX measures. Ex: I want to know number of rows and sum of a specific column for every table I have imported (assuming each table has the same column to sum)

Let's say I have 3 tables so far...

  • Table1
  • Table2
  • Table3

Let's say I have two measure...

  1. NumberOfRows
  2. ColumnSum

Let's say I have a created table that lists the tables...

TableIDTable Name
1Table2
2Table2
3Table3



WHAT I CURRENTLY HAVE:

The measures look like this...

NumberOfRows = SWITCH(Table[ID], 1, COUNTROWS(Table1), 2, COUNTROWS(Table2), 3, COUNTROWS(Table3))
ColumnSum = SWITCH(Table[ID], 1, SUM(Table1[Column]), 2, SUM(Table2[Column]), 3, SUM(Table3[Column]))

Currently, if I add new tables to my report, I add the new table expression to each measure! 


WHAT I WANT:

To only have to update one DAX measure and all the other measure update as well. 

EX: having a dax measure called "TableUsed" and all my measures use that table

TableUsed = SWITCH(1, Table1, 2, Table2, 3, Table3)
NumberOfRows = COUNTROWS(TableUsed)
ColumnSum = SUM(TableUsed)

This way when I add new tables, all I have to update is the TableUsed measure, and not every single measure it uses.
Right now I don't know how to havea table be the return value of a measure or how to create any sort of macros / functions for DAX formulas to dynamically change the "text" used in the DAX. 

 

If anyone knows any solution or has any input... Please provide! Thank you. 

4 REPLIES 4
tamerj1
Super User
Super User

@spencerhoyle10 

Do all tables have the same columns? What differences are there between the tables?

The tables are all different (though they may be related to one another).

However, I am creating a single column in each of them that has the same name. I want to do similar measures on that column across all the tables. 

@spencerhoyle10 

Reality is always different, however let's assume only one column is involved in the measures. What I would do it to use power query to create a table that selects that column from all of these tables adding the table name as new columns then append in one table. 
in this case the table slicer will be a column in the main table, clicking on a table name will filter the main table down to that table and hence any sum our conunt measure related to that table. 
of course the that might not be ideal as the model and filter requirements from other columns in the original tables shall make this solution not practical or even not applicable. 

other approach can be adding the table name or index as a column to each table then create a table that contains all table names/indexes and then create relationships with the tables. That would be provide more flexibility in respecting other filters in the model. 

tamerj1
Super User
Super User

@spencerhoyle10 

I've been always hoping that one day DAX will have functions like IFTABLE or SWITCHTABLE or even tableX functions like UNIONX or EXCEPTX. Unfortunately, as of now this looks more like a dream. What youylooking for is currently not possible. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors