Forum Discussion

mastone's avatar
mastone
Frequent Visitor
3 years ago
Solved

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

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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]
        )
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    mastone
    Frequent Visitor
    3 years ago

    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.