Is there an alternative for this conditional column solution?

[MHoque]

Hi,

I am trying to answer an analysis question for the Online Chess Game dataset on https://www.mavenanalytics.io/data-playground

I am trying to answer the below query,

"What percentage of games are won by the player with the higher rating?"

The ratings are presented like below,

I have been able to output the winners into a conditional column (higher/lower) via power query (transform data)  - I can then calculate the percentages with measures into something like this,

This has worked for me however I am aware that this can impact performance as this increases the size of the overall file.

What would be the best practice in this instance? Is there a way to work around this with measures?

Thanks in advance for any assistance/advice,

Miz

[AntonioM · Accepted solution]

You could write that as a measure, something like 

Higher % =
VAR higher =
    SUMX (
        Table,
        IF (
            ( Table[Winner] = "White"
                && Table[white_rating] > Table[black_rating] )
                || ( Table[Winner] = "Black"
                && Table[white_rating] < Table[black_rating] ),
            1
        )
    )
RETURN
    DIVIDE ( higher, [Total Games] )

and then format that as %

[MHoque]

Thank you AntonioM - that worked 😊

A follow up query,

If I have the winner column (winner) and player rating columns (white_rating, black_rating) stored in separate dimension tables. How would I reference both tables working with a measure like the above?

Thanks again - I'll mark it as a solution!

[AntonioM]

You shoud be able to use RELATED and then sum over the fact table

SUMX (
        FactTable,
        IF (
            ( RELATED(DimTable1[Winner]) = "White"
                && RELATED(DimTable2[white_rating]) > RELATED(DimTable2[black_rating]) )
                || ( RELATED(DimTable1[Winner] = "Black"
                && RELATED(DimTable2[white_rating]) < RELATED(DimTable2[black_rating]) ),
            1
        )
    )

[MHoque]

That worked thank you (just needed to add a closing bracket after "black").

I am still relatively new to Power BI/DAX but you have taught me quite a bit about using RELATED and IF - Thanks again!

[AntonioM]

Yes - I missed that one.

No worries!