locked
select max record from group based on filter(s) RRS feed

  • Question

  • Hi,

    Anytime a Location Dimension record is added, a new record is inserted into the Fact Table. Also, any time a value changes for a location, a new record is inserted into the fact table.

    We are using PowerView over this model and would like to be able to use filters for both the AsOfDate and EffectiveDate to return the max row for each location based on the date filters entered.

    For example, right now if we create a filter for both the AsOfDate and EffectiveDt and use 4-16-2014 for both filters, the following records are returned:

    What we want is to just get FactValueId of 301 since it is the max record for the location that meets the filter critieria given.

    I'm guessing some DAX will have to be scripted, but wasn't sure if this was even possible given our schema.

    thanks

    Scott

    Friday, October 24, 2014 3:41 PM

Answers

  • (Thanks Greg! )

    Scott, here's the new version based on TOPN - I like this one much better OnlyMax :=
    CALCULATE (
        COUNTROWS ( FactValues ),
        TOPN (
            1,
            ALLSELECTED ( FactValues ),
            FactValues[FactValueID],
            0
        ),
        VALUES ( FactValues[FactValueID] )
    )

    TOPN()
    Scans the 2nd Parameter = ALLSELECTED(FactValues)
    Orders them based on 3rd Parameter = FactValues[FactValueId]
    Returns the 1 row (based on 1st Parameter) = 1

    Then I add the VALUES(FactValues[FactValueID]) back in there in order to restrict the results to original FactValueId

    For additional help on these functions - you can check out MSDN TOPN, ALLSELECTED
    You may also find helpful specific examples of TOPN, ALLSELECTED


    Regards, Avi www.powerpivotpro.com

    • Proposed as answer by greggyb Tuesday, October 28, 2014 8:55 PM
    • Marked as answer by Charlie Liao Friday, October 31, 2014 11:16 AM
    Tuesday, October 28, 2014 8:55 PM

All replies

  • Scott,

    Define a new measure which returns BLANK() for all but the MAX row.
    Try ones below, add OnlyMax to the Table, that should filter it down to one row only.


    CalcMax :=
    CALCULATE (
        MAX ( FactValues[FactValueID] ),
        ALLSELECTED ( FactValues[FactValueID] )
    )
    OnlyMax :=
    IF (
        HASONEVALUE ( FactValues[FactValueID] ),
        IF (
            VALUES ( FactValues[FactValueID] ) = [CalcMax],
            TRUE,
            BLANK ()
        )
    )


    p.s. Experts, is there a better way to do this?

    Regards, Avi www.powerpivotpro.com

    Tuesday, October 28, 2014 4:26 AM
  • Try writing any measures you need as follows:

    Only for most recent measure:=
    CALCULATE(
        <some expression>
        , TOPN(
            1
            , <fact table>
            , <order by field>
            , 0
        )
        , [ <filters> ]
    )

    This will calculate your measure in the context of only the most recent row in the fact table (most recent based on your order by field).

    If the only purpose is to restrict the results, I'll typically use a COUNTROWS() as my expression.

    Tuesday, October 28, 2014 4:41 PM
  • (Thanks Greg! )

    Scott, here's the new version based on TOPN - I like this one much better OnlyMax :=
    CALCULATE (
        COUNTROWS ( FactValues ),
        TOPN (
            1,
            ALLSELECTED ( FactValues ),
            FactValues[FactValueID],
            0
        ),
        VALUES ( FactValues[FactValueID] )
    )

    TOPN()
    Scans the 2nd Parameter = ALLSELECTED(FactValues)
    Orders them based on 3rd Parameter = FactValues[FactValueId]
    Returns the 1 row (based on 1st Parameter) = 1

    Then I add the VALUES(FactValues[FactValueID]) back in there in order to restrict the results to original FactValueId

    For additional help on these functions - you can check out MSDN TOPN, ALLSELECTED
    You may also find helpful specific examples of TOPN, ALLSELECTED


    Regards, Avi www.powerpivotpro.com

    • Proposed as answer by greggyb Tuesday, October 28, 2014 8:55 PM
    • Marked as answer by Charlie Liao Friday, October 31, 2014 11:16 AM
    Tuesday, October 28, 2014 8:55 PM
  • thanks Greg2178,

    So would this measure be created on the fact table?  Also, how would i use it in PowerView?  Would i have to drag it as a field on the table or can I use it as a filter?

    thanks

    Scott

    Friday, October 31, 2014 5:17 PM
  • Scott,

    You can add it as a field in the table.
    You can also use it as a filter (set it to show rows where value IS NOT BLANK.


    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response

    Saturday, November 1, 2014 7:27 AM
  • Measures can be added to any table. The most common reason to choose one table over another is for logical separation of measures.

    The only feature related to Power Pivot/Tabular that is affected by which table you add a measure to is pivot table drill-through (double clicking on a Values cell, or right clicking and choosing 'Show Details'), as the resultset for drill-through is determined by the table that the measure lives in.

    Also, it was Avichal who provided the measure marked as an answer. I just proposed it at as an answer for the thread.

    Monday, November 3, 2014 2:56 AM