Forum Discussion

Khalefa's avatar
Khalefa
Helper 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
Community Champion
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]
)
Khalefa's avatar
Khalefa
Helper I
3 years ago

tamerj1  it worked fine, thanks a lot for the support