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]
)
v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi, Khalefa 

Oh..According to your description, i get it that you just want to convert the calculated column to Measure , Right?

If this , you can try to use this dax as a measure:

 

Weighted score =

var _cur_ID = MAX('Fact'[Review ID])

var _t =FILTER( ALLSELECTED( 'Fact') , [Review ID] = _cur_ID)

var _t2 = ADDCOLUMNS( _t , "flag" , var _score =SELECTCOLUMNS( FILTER(_t , [Attribute] in {"Compliance","Image"}) , "Score",[Score]) return IF({0} in _score , 0 ,1))

var _t3 = ADDCOLUMNS( _t2 , "weighted_score" , var _flag = [flag] var _score = [Score] var _weight = RELATED('Dim_Attribute'[Weight]) return IF(_flag=0,0,IF(_score =0 , 0, _weight)))

return

SUMX(_t3, [weighted_score])

Then we can meet your need and we can keep the condition with the Category slicer , like this:

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Khalefa's avatar
Khalefa
Helper I
3 years ago

v-yueyunzh-msft  thanks for the effort. it looks very structured but it still didn't give the required numbers as the total should = 145 and if I added the category below the review id in a table visual, it shouldn't give the total score of the review repeated in every category row

and it you could explain the steps to follow a long will be much appreciated, thanks again