Forum Discussion
INDIRECT function in DAX
Hi all, I have a problem. I want to create a dynamic measure in DAX that will depend of the name of the variable. I have a table with column names display like this Month 1, Month 2,..... Month n, ...
Hi Anonymous ,
I'm assuming that your data is on the format like below:
| KPI | 1 | 2 | 3 | 4 | 5 | 6 |
| Value | 1 | 1 | 1 | 1 | 1 | 1 |
If this is the case believe that the best option is to unpivot the table and have something like this:
| KPI | Month | Value |
| Value | 1 | 1 |
| Value | 2 | 2 |
| Value | 3 | 3 |
| Value | 4 | 4 |
| Value | 5 | 5 |
| Value | 6 | 6 |
Then you can create a measure similar to this:
Total 2 month =
CALCULATE ( SUM ( Table1[Value] ) )
+ CALCULATE (
SUM ( Table1[Value] );
FILTER ( ALL ( Table1[Month] ); Table1[Month] = ( MAX ( Table1[Month] ) - 1 ) )
)
If you don't want to unpivot your columns then you need to do it using several measures and a switch function.
Can you share a sample of you data and expected result.
thank you.
Regards,
MFelix