Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I want to be able to multiply row values from a table by the column values, in this case the column value is the month number/period of each column.
So if the row value is 2 then in period 4 it will equal 8 and in period 5 it will equal 10 and so on. What DAX expression can I use to get the current Column value of a matrix in order to use that value to mutliply it by a row value. Here is some sample test data
TABLE1 | ||||||||||||
Category | Amount | Month | ||||||||||
A | 5 | 1 | ||||||||||
B | 10 | 2 | ||||||||||
C | 4 | 5 | ||||||||||
D | 6 | 7 | ||||||||||
MATRIX | ||||||||||||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
A | 5 | 10 | 15 | 20 | 25 | 30 | 35 | 40 | 45 | 50 | 55 | 60 |
B | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 | |
C | 20 | 24 | 28 | 32 | 36 | 40 | 44 | 48 | ||||
D | 42 | 48 | 54 | 60 | 66 | 72 |
Solved! Go to Solution.
@Greg_Deckler thanks for taking the time to reply. I was able to accomplish what I needed by wrapping the max inside calculate and putting it in the right context.
VAR __period = CALCULATE ( MAX ( ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID] ), USERELATIONSHIP ( ACCOUNTING_PERIODS[YearMonthNo], 'Date'[YearMonthNo] ) )
Just use MAX([Column]) to get the current value of your column in your matrix.
Hi @Greg_Deckler
Thats what I had but the MAX(TABLE1[Month]) is giving me that max period for the row. For example with MAX(TABLE1[Month]) from the example above, I get 1 for the Column that is then multiply by value 5 of row A . But I need to be able to multiple the value 5 of A by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, and 12. Not sure If I explained myself correctly.
@Greg_Deckler thanks for taking the time to reply. I was able to accomplish what I needed by wrapping the max inside calculate and putting it in the right context.
VAR __period = CALCULATE ( MAX ( ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID] ), USERELATIONSHIP ( ACCOUNTING_PERIODS[YearMonthNo], 'Date'[YearMonthNo] ) )
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |