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... 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.
DATEADD doesn't work with cumulative sum
Hello. I've got the next problem - i want to calculate WoW, and because there isn't any function i use DATEADD function to calculate values a week ago. But there is a problem i can't solve - it doesn't work with cumulative values (i guess it's the reason of problem). There are 4 measures (for convenience i put them together, also don't mind error lines - i just translated names to English, first 3 work totally fine) - the first one to sum working hours, the second one to gain cumulative sum, the third one to substract cumulative sum from a digit (20), and the forth one should show data a week ago. But it doesn't work properly. For convenience i used 0 interval in DATEADD - the data should be as in the third column, but for some reason it substracts the first column and not the second one as it should be. I'll be very glad if you'll help me to solve this problem (or will give another solution). Have a nice day anyway.
