topic Re: DAX macro / dynamically switch table to be used in measures in DAX Commands and Tips

DAX macro / dynamically switch table to be used in measures

spencerhoyle10 — Fri, 10 Mar 2023 18:11:17 GMT

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

NumberOfRows
ColumnSum

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

TableID	Table Name
1	Table2
2	Table2
3	Table3

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.

Re: DAX macro / dynamically switch table to be used in measures

tamerj1 — Fri, 10 Mar 2023 18:37:56 GMT

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.

Re: DAX macro / dynamically switch table to be used in measures

tamerj1 — Fri, 10 Mar 2023 19:02:08 GMT

spencerhoyle10

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

Re: DAX macro / dynamically switch table to be used in measures

spencerhoyle10 — Fri, 10 Mar 2023 19:54:04 GMT

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.

Re: DAX macro / dynamically switch table to be used in measures

tamerj1 — Fri, 10 Mar 2023 20:39:14 GMT

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.