Forum Discussion
Summarize, SummarizeColumns or a 3rd option
Hi, I am currently trying to solve a table manipulation issue i DAX. The problem is; I would like to take the last value in the period (Dec = 35) and use this as constant in a calculation where I co...
Since you have a tabular model, I assume that you don't have to use month names for determening the last month, but can use something like month number or date:
ComparisonMeasure = VAR mxMon = CALCULATE ( MAX ( 'Table'[Month] ); ALL ( 'Table' ) ) VAR mxVal = CALCULATE ( VALUES ( 'Table'[Values] ); FILTER ( ALL ( 'Table' ); 'Table'[Month] = mxMon ) ) RETURN COUNTROWS ( FILTER ( 'Table'; 'Table'[Values] > mxVal ) )
sturlaws
Resident Rockstar
Resident RockstarHi,
Do you want to add a calculated column, or do you need a temporary table in a measure?
For a calculated column you can do this
Comparison =
VAR mxMon =
CALCULATE ( MAX ( 'Table'[Month] ); ALL ( 'Table' ) )
VAR mxVal =
CALCULATE (
VALUES ( 'Table'[Values] );
FILTER ( ALL ( 'Table' ); 'Table'[Month] = mxMon )
)
RETURN
IF ( 'Table'[Values] > mxVal; 1; 0 )cheers,
S
Hi,
Since we are running on a tabular model and have variable period start and end i am thinking temporary table in measure.
Thanks
- sturlaws
Since you have a tabular model, I assume that you don't have to use month names for determening the last month, but can use something like month number or date:
ComparisonMeasure = VAR mxMon = CALCULATE ( MAX ( 'Table'[Month] ); ALL ( 'Table' ) ) VAR mxVal = CALCULATE ( VALUES ( 'Table'[Values] ); FILTER ( ALL ( 'Table' ); 'Table'[Month] = mxMon ) ) RETURN COUNTROWS ( FILTER ( 'Table'; 'Table'[Values] > mxVal ) )Correct, number or date are both available.
- sturlaws
So then you have everything you need to solve it?