Trihybrid Crosses (Genetics) 01-18-2023 19:06 PM

[Greg_Deckler]

This is really kind of a showcase of the issues created by Power BI's "case preemptivity". Power BI's data model isn't really case insensitive per se but it does exhibit case insensitivity in particular circumstances where duplicate strings with different cases are present in the data model. In such cases, the first string "wins" if you will and all other versions of that string with different casing are treated as if they have the exact same casing of the first string. It's weird and it's a real issue in certain circumstances. The hoops you have to jump through to preserve the original casing are absurd. Note that this is a real issue with certain data like SKU numbers and other data where the original casing is super important. 

Crossed = 
    VAR __Row = MAX('Rows'[Genes])
    VAR __Column = MAX('Columns'[Genes])
    VAR __LenRow = LEN(__Row)
    VAR __LenColumn = LEN(__Column)
    VAR __FirstGeneRow = 
        IF(
            __LenRow = 3,
            LEFT(__Row,1),
                VAR __Char = FIND(UNICHAR(8203),__Row)
                VAR __Result = IF(__Char = 2, LEFT(__Row, 2), LEFT(__Row,1))
            RETURN
                __Result
        )
    VAR __FirstGeneColumn = 
        IF(
            __LenColumn = 3,
            LEFT(__Row,1),
                VAR __Char = FIND(UNICHAR(8203),__Column)
                VAR __Result = IF(__Char = 2, LEFT(__Column, 2), LEFT(__Column,1))
            RETURN
                __Result
        )
    VAR __SecondGeneRow = 
        IF(
            __LenRow = 3,
            MID(__Row,2,1),
                VAR __Char = FIND(UNICHAR(8203), __Row, LEN(__FirstGeneRow)+1,-1)
                VAR __Result = IF(__Char <> LEN(__Row) && __Char <> -1, MID(__Row, __Char - 1, 2), MID(__Row, LEN(__FirstGeneRow)+1,1))
            RETURN
                __Result
        )
    VAR __SecondGeneColumn = 
        IF(
            __LenColumn = 3,
            MID(__Column,2,1),
                VAR __Char = FIND(UNICHAR(8203), __Column, LEN(__FirstGeneColumn)+1,-1)
                VAR __Result = IF(__Char <> LEN(__Column) && __Char <> -1, MID(__Column, __Char - 1, 2), MID(__Column, LEN(__FirstGeneColumn)+1,1))
            RETURN
                __Result
        )
    VAR __FirstGenePair = 
        IF(
            LEN(__FirstGeneRow) > LEN(__FirstGeneColumn),
            __FirstGeneRow & __FirstGeneColumn,
            __FirstGeneColumn & __FirstGeneRow
        )
    VAR __SecondGenePair = 
        IF(
            LEN(__SecondGeneRow) > LEN(__SecondGeneColumn),
            __SecondGeneRow & __SecondGeneColumn,
            __SecondGeneColumn & __SecondGeneRow
        )
    VAR __ThirdGeneRow = 
        IF(
            __LenRow = 3,
            RIGHT(__Row,1),
                VAR __Char = FIND(UNICHAR(8203), __Row, LEN(__FirstGeneRow) + LEN(__SecondGeneRow) + 1, -1)
                VAR __Result = IF(__Char <> -1, RIGHT(__Row, 2), RIGHT(__Row, 1))
            RETURN
                __Result
        )
    VAR __ThirdGeneColumn = 
        IF(
            __LenColumn = 3,
            RIGHT(__Column,1),
                VAR __Char = FIND(UNICHAR(8203), __Column, LEN(__FirstGeneColumn) + LEN(__SecondGeneColumn) + 1, -1)
                VAR __Result = IF(__Char <> -1, RIGHT(__Column, 2), RIGHT(__Column, 1))
            RETURN
                __Result
        )
    VAR __ThirdGenePair = 
        IF(
            LEN(__ThirdGeneRow) > LEN(__ThirdGeneColumn),
            __ThirdGeneRow & __ThirdGeneColumn,
            __ThirdGeneColumn & __ThirdGeneRow
        )
    VAR __FirstGeneProperty = MAXX(FILTER('Gene Pair Properties', [Pair] = __FirstGenePair),[Property])
    VAR __SecondGeneProperty = MAXX(FILTER('Gene Pair Properties', [Pair] = __SecondGenePair),[Property])
    VAR __ThirdGeneProperty = MAXX(FILTER('Gene Pair Properties', [Pair] = __ThirdGenePair),[Property])
    VAR __Result = 
        __FirstGeneProperty & UNICHAR(10) & UNICHAR(13) & 
        __SecondGeneProperty & UNICHAR(10) & UNICHAR(13) & 
        __ThirdGeneProperty & UNICHAR(10) & UNICHAR(13) & 
        __FirstGenePair & __SecondGenePair & __ThirdGenePair
RETURN
    __Result

And the video:

eyJrIjoiMmFhYWM4MTgtMDRmNS00MjQ4LThlNTMtM2NhODVmYjNmODY2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9