Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

 

 

 

 

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

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

5 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

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