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 
I hope this is what you're looking for. Please refer to attached sample file.

% Score = 
AVERAGEX ( 
    VALUES ( 'Fact'[Review ID] ),
    IF (
        0
            IN CALCULATETABLE (
                VALUES ( 'Fact'[Score] ),
                Dim_Attribute[Attribute] IN { "Compliance", "Image" }
            ),
        0,
        SUMX ( CALCULATETABLE ( 'Fact' ), 'Fact'[Score] * RELATED ( Dim_Attribute[Weight] ) / 100 ) / 100
    )
)

 

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

    tamerj1  can i ask why did you use calculatetable after sumx rather than just sumx
    on the other side, you referred to attribute from dim table rahter than attribute from fact. will this matter ?

    Thanks for your patiance

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Khalefa 

      CALCULATETABLE is required for context transition otherwise 'Fact' will be filtered only as per the original filter context filter context i.e.  the filters on the visual, slicers, filter pane etc.. while we need the table filtered by the currently iterated Review ID. 

      Regarding your 2nd question, the answer is no, it makes no difference