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

    CNENFRNL  thanks for the code yet i would need to understand why did add the overall weighted DAX to another iteration in overall DAX, rather than doing one iteration in one time.
    another part, the weight column is in a dimension table with a unique list of attributes

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi , Khalefa 

      I see that the CNENFRNL  have been realized in his dax. But for your "another part, the weight column is in a dimension table with a unique list of attributes". But what  dimension table 's structure in your side?

      So is there a relationship between your dimension table and your sample data?
      Can you provide your original table structure? For example, provide your dimension table in the form of a table?

       

      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