Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |