Summarize, SummarizeColumns or a 3rd option

[Anonymous]

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 compare Value and Contant to see how many values are higher than the constant (Result = 5).

We are running on a tabular model so this would have to be done in DAX. The period start and end is variable and I would like to have the solution respect the slicers used in Power BI.

I have been investigating SUMMARIZE, SUMMARIZECOLUMNS and GROUPBY but have yet to find a good solution.

[sturlaws]

Hi,

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

[Anonymous]

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 · Accepted solution]

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 ) )

[Anonymous]

Correct, number or date are both available.

[sturlaws]

So then you have everything you need to solve it?