Convert Table Expression (i.e. virtual table) to a set of Measures

[mastone]

New to DAX and power BI.  I am struggling and want to go hide under the covers.   Anywhoooo, after many iterations I have been able to generate a virtual table that produces the results I want.  But I want it in a measure.  The complexity comes in via the fact that I need to do calculations on measures several layers deep.

Here is my "measure" so far (it doesn't work because I can't figure out how return the value as a scalar)  Code is also in screenshot below for readability.

Impact = 
VAR countfailureTable =
    GROUPBY(
        FILTER( ALLSELECTED(Surveys), Surveys[SVRESULT] >= 0 ),
        Surveys[QuestionId],
        "QuestionCount", COUNTX( CURRENTGROUP(), Surveys[QuestionId] ),
        "FailurePercent", AVERAGEX( CURRENTGROUP(), Surveys[SVRESULT] )
    )
VAR allItems =
    ADDCOLUMNS(
        countfailureTable,
        "NegativeImpact",
            (
                [QuestionCount] / SUMX( countfailureTable, [QuestionCount] ) * [FailurePercent]
            )
    )
VAR finalItems =
    ADDCOLUMNS(
        allItems,
        "RelativeImpact", [NegativeImpact] / MAXX( allItems, [NegativeImpact] )
    )

If I take the above and create a new table from it in my model, it produces the correct result (aside from not having the row and filter contexts).  The table looks like this with the underlined columns being the data I want.

What I can't figure out is how to associate the [QuestionId] in my virtual table called [FinalItems] to the QuestionId field in the current row context of the visualization where the measure is used.  Furthermore, I feel (but can't figure out how to confirm) that the rows used by the GROUPBY does not have the current row context (even though I used ALLSELECTED).  So how do I lookup the [RelativeImpact] field using the row context [QuestionId] field???

I feel like my virtual table above needs to be converted into a series of measures, but I don't see a way to do it.  So if there is a completely better approach, please don't hesitate to scrap my many many days of work and suggest something new.  🙂

[tamerj1 · Accepted solution]

Hi @mastone 
Please try

ImpactMeasure =
VAR countfailureTable =
    GROUPBY (
        FILTER ( ALLSELECTED ( Surveys ), Surveys[SVRESULT] >= 0 ),
        Surveys[QuestionId],
        "QuestionCount", COUNTX ( CURRENTGROUP (), Surveys[QuestionId] ),
        "FailurePercent", AVERAGEX ( CURRENTGROUP (), Surveys[SVRESULT] )
    )
VAR allItems =
    ADDCOLUMNS (
        countfailureTable,
        "NegativeImpact",
            (
                [QuestionCount] / SUMX ( countfailureTable, [QuestionCount] ) * [FailurePercent]
            )
    )
VAR finalItems =
    ADDCOLUMNS (
        allItems,
        "RelativeImpact", [NegativeImpact] / MAXX ( allItems, [NegativeImpact] )
    )
RETURN
    AVERAGEX (
        VALUES ( Surveys[QuestionId] ),
        MAXX (
            FILTER ( finalItems, [QuestionId] = EARLIER ( [QuestionId] ) ),
            [RelativeImpact]
        )
    )

[mastone]

Thank you so much tamerj1!  This did the trick with the slight modification to filter records instead of the VALUES() call.  It ended up looking like this and works as expected:

    MAXX(
        FILTER(
             Surveys ,
            Surveys[SVRESULT] >= 0
                && Surveys[SVRESULT] < 100
        ),
        MAXX(
            FILTER( finalItems, [QuestionId] = EARLIER( [QuestionId] ) ),
            [RelativeImpact]
        )
    )

Question for you.  Is this a common pattern?  I keep finding myself having to do aggregate measures based other aggregate measures and I run into problems.  Is the fact that I keep running into measures on measures an indication that I am doing something wrong?  Could that be a red flag?  Or is it a common pattern to have this sort of thing?

Thanks again for you help.  I was so frustrated.   Taking the average of a max that produces a single value seems odd, but it logically makes sense and wrangles the table into a scalar...which is what I needed.