Calculating an average and showing a dynamic value from a joined table

[Housden996]

Hi

I have a data set where a review score out of ten is submitted and corresponds to a unique ID:

I have calculated the Average of the scores and now I want to show this score agasint a corresponding table which has a ratings stored within it:

I have tried to use DAX to create a measure as follows and then return the value which is assocaited but I am not having any luck:

Average Validation Score Average Grouped =

switch(

    TRUE,

        [AVG Validation Rating] = 1 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 2 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 3 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 4 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 5 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 6 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 7 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 8 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 9 , MAX('Validation Score'[Word]),

        [AVG Validation Rating] = 10 , MAX('Validation Score'[Word])

    )

Which fucntion should I use instead of MAX to get the associated word form the table and display this on a card?

[v-binbinyu-msft · Accepted solution]

Hi @Housden996 ,

Pleae try below dax formula:

Average Validation Score Average Grouped =
SWITCH (
    TRUE,
    [AVG Validation Rating] = 1, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 1 ),
    [AVG Validation Rating] = 2, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 2 ),
    [AVG Validation Rating] = 3, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 3 ),
    [AVG Validation Rating] = 4, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 4 ),
    [AVG Validation Rating] = 5, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 5 ),
    [AVG Validation Rating] = 6, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 6 ),
    [AVG Validation Rating] = 7, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 7 ),
    [AVG Validation Rating] = 8, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 8 ),
    [AVG Validation Rating] = 9, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 9 ),
    [AVG Validation Rating] = 10, CALCULATE ( MAX ( 'Validation Score'[Word] ), 'Validation Score'[Score] = 10 )
)

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.