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

I just want to get the total score from your solution without percentage and another DAX for the total weight. this is what I meant.

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Khalefa 

Thanks for your quick response and your sample .pbix file to us!

Here are the steps you can refer to :
(1)We can create a calculated column in 'Fact' Table:

Weighted score = var _cur_ID = [Review ID]
var _t =SELECTCOLUMNS( FILTER('Fact' , 'Fact'[Review ID] = _cur_ID && 'Fact'[Attribute] in {"Compliance","Image"}) , "Score",[Score])
var _weight = RELATED('Dim_Attribute'[Weight])
var _cur_sore = [Score] 
return
IF( {0} in _t , 0 ,  IF(_cur_sore=0,0, _weight))

(2)Then we can create these measures:

Total Weight = SUMX( VALUES('Fact'), RELATED('Dim_Attribute'[Weight]))
Total Weight 2 = SUMX( ALL('Fact'), RELATED('Dim_Attribute'[Weight]))
Score % = SUM('Fact'[Weighted score]) / [Total Weight]

(3)Then we can put the fieds we need on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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

 

    • 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