Reference variable tables using dynamic table names - Power BI, DAX

Question

I have a measure whereby I calculate some data based on a slicer value. The slicer determines which table I get my data from e.g. SUM(TableA[sales]) or SUM(TableB[sales]). (Each of my tables is of the same structure).

I'd like to be able to set up a variable to dynamically change (with my slicer) which table I'm referring to.

Right now I have:

// Set __table to a stored string representation of the name of the database
// This seems to work
VAR __table = SELECTEDVALUE( AllTables[TableNames] )

// Evaluate the sales column of my variable table
// This doesn't work due to 'Cannot find name sales'
CALCULATE(SUM(__table[sales]))

I guess what I'm after is a way to have the slicer change the referenced table in my measures (with all of the columns being the same for each table).

I'm new to Power BI and I've been struggling with this for several days now. I'd really appreciate any help with either the method I'm attempting or any alternatives.

I don't think there's a way to do exactly what you're after but you don't usually need to do this with a good model. Why do you need to switch among tables and approximately how many tables are you trying to switch between? — Alexis Olson
– Alexis Olson, Commented Sep 15, 2021 at 22:37
@AlexisOlson Hi, could be up to about 20 tables. It's for data gathered from devices in different places around an office, where each device is imported as its own table. The user can then switch between seeing the data from the different data tables on their views by using a slicer. — 125125
– 125125, Commented Sep 15, 2021 at 22:45

mkRabbani · Accepted Answer · 2021-09-16 03:46:14Z

1

If you have small amount of data in each table, you can merge them all together as they are in same structure. Just add an additional column with reference of the Table name. Now you can create your Slicer using the new column and it will hold all table names in the list. Now, if you change table name in the slicer, your visuals will show results based on data from that respected table.

answered Sep 16, 2021 at 3:46

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Knut Boehnert Over a year ago

In case the original tables shall work as just now then a view with an extra source table column with UNION ALL between all the tables adds a more dynamic layer.

125125 Over a year ago

Hey, thanks for the advice. Would this work for larger tables (20000+ records added per day) or would I have issues?

mkRabbani Over a year ago

unless you have millions of records all together, there is no issue. For your case, you can also think about period like last 12 or 36 month's data to be added in the new table. This depends on your requirement. But you can remove older data that never shown in the visual.

125125 Over a year ago

I'll attempt doing it this way. Thank you for your help.

Collectives™ on Stack Overflow

Reference variable tables using dynamic table names - Power BI, DAX

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related