Forum Discussion

Khalefa's avatar
Khalefa
Icon for Helper I rankHelper I
3 years ago
Solved

Iteration with multiple column conditions

Hi team Need support in the below a measure to calculate the overall attribute score on the review ID granularity considering the below:- 1- There are 6 attributes in each review id 2- The DAX sh...
Need Help DAX measure
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Khalefa 
    Please refer to attached amended sample file

    Weighted Score = 
SUMX (
    VALUES ( 'Fact'[Review ID] ),
    IF (
        0
            IN CALCULATETABLE (
                VALUES ( 'Fact'[Score] ),
                ALL ( Dim_Attribute[Attribute] ),
                Dim_Attribute[Attribute] IN { "Compliance", "Image" }
            ),
        0,
        SUMX (
            CALCULATETABLE ( 'Fact' ),
            'Fact'[Score] * RELATED ( Dim_Attribute[Weight] ) / 100
        )
    )
)
    Total Weight = 
SUMX (
    VALUES ( 'Fact'[Review ID] ),
    SUMX (
        CALCULATETABLE ( 'Fact' ),
        RELATED ( Dim_Attribute[Weight] ) 
    )
)
    % Score = 
DIVIDE (
    [Weighted Score],
    [Total Weight]
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Khalefa 
Please refer to attached sample file with the solution (2 options)

% Score = 
AVERAGEX ( 
    VALUES ( Scores[Review ID] ),
    IF (
        0
            IN CALCULATETABLE (
                VALUES ( Scores[Score] ),
                Scores[Attribute] IN { "Compliance", "Image" }
            ),
        0,
        SUMX ( CALCULATETABLE ( Scores ), Scores[Score] * RELATED ( Weights[Weight] ) / 100 ) / 100
    )
)
% Score2 = 
AVERAGEX ( 
    VALUES ( Scores[Review ID] ),
    VAR T1 = CALCULATETABLE ( Scores )
    RETURN
    IF (
        ISEMPTY ( 
            FILTER (
                T1,
                Scores[Attribute] IN { "Compliance", "Image" }
                    && Scores[Score] = 0
            )
        ),
        SUMX ( T1, Scores[Score] * RELATED ( Weights[Weight] ) / 100 ) / 100,
        0
    )
)