DAX - PowerBi - 从数组中严格匹配子集字符串并为其赋值 [英] DAX - PowerBi - Match subset string strictly from array and assign value to it

问题描述

Column = 
    VAR _1 =
        ADDCOLUMNS ( 'digital_competences_q3', "new", SUBSTITUTE ( digital_competences_q3[q3_a], ";", "|" ) )
    VAR _2 =
        GENERATE (
            _1,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
                "_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
            )
        )
    VAR _3 =
        ADDCOLUMNS (
            _2,
            "score", MAXX ( FILTER ( 'lookup', EARLIER ( [_txt] ) = 'lookup'[Column1] ), 'lookup'[score] )
        )
    RETURN
        CALCULATE (
            MAXX (
                ADDCOLUMNS (
                    'digital_competences_q3',
                    "score", SUMX ( FILTER ( _3, EARLIER ( digital_competences_q3[q3_a] ) = [q3_a] ), [score] )
                ),
                [score]
            )
        )

从@smpa01 慷慨提供的代码开始，用户可以从这个数组中选择任何选项:Windows 操作系统的计算机；MAC 操作系统的计算机；Android 操作系统的电话；电话iOS操作系统；平板电脑；

moving from this code which was so graciously provided by @smpa01 the user can select any of the options from this array : Computers with Windows operating system ;Computers with MAC operating system;Phones with Android operating system;Phones with iOS operating system;Tablets;

单独的选择是:

• 装有 Windows 操作系统的计算机
• 装有 MAC 操作系统的计算机
• 搭载 Android 操作系统的手机
• 装有 iOS 操作系统的手机
• 平板电脑

在用户选择他们的选择(可以是多个)后，创建的数组将包含由;"分隔的选项

after the user selects their choice (which can be multiple) the array created will contain the options separated by ";"

主表的列选择了数组查找表具有分配给每个选项的值 (2) 的选项.

the main table has the column with the array selected the lookup table has the options with the value (2) assigned to each one.

如果选择了所有选项，则总和为 20 而不是 10.

if all options are selected the sum is 20 instead of 10.

所以问题是，它是否与查找表 column1 中的整个值匹配?还是基于常用词?我们可以看到单个 IOS 值得到了正确的评分.

so the question is, is it matching the entire value from the lookup table column1? or based on common words? as we can see the single IOS value gets scored correctly.

推荐答案

Column =
VAR _1 =
    ADDCOLUMNS (
        'digital_competences_q3',
        "new", SUBSTITUTE ( digital_competences_q3[q3_a], ";", "|" )
    )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
            "_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
        )
    )
VAR _3 =
    ADDCOLUMNS (
        _2,
        "score",
            MAXX (
                FILTER ( '_lookup', EARLIER ( [_txt] ) = '_lookup'[Column1] ),
                '_lookup'[score]
            )
    )
VAR _4 =
    CALCULATE ( COUNTX ( digital_competences_q3, digital_competences_q3[q3_a] ) )
VAR _5 =
    CALCULATE (
        MAXX (
            ADDCOLUMNS (
                'digital_competences_q3',
                "score",
                    SUMX (
                        FILTER ( _3, EARLIER ( digital_competences_q3[q3_a] ) = [q3_a] ),
                        [score]
                    )
            ),
            [score]
        )
    )
RETURN
    DIVIDE ( _5, _4 )

编辑

Column = 
    VAR _1 =
        ADDCOLUMNS ( 'digital_competences_q3', "new", SUBSTITUTE ( digital_competences_q3[q3_a], ";", "|" ) )
    VAR _2 =
        GENERATE (
            _1,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
                "_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
            )
        )
    VAR _3 =
        ADDCOLUMNS (
            _2,
            "score", MAXX ( FILTER ( '_lookup', EARLIER ( [_txt] ) = '_lookup'[Column1] ), '_lookup'[score] )
        )
    VAR _4 = CALCULATE(COUNT(digital_competences_q3[q3_a]),ALLEXCEPT(digital_competences_q3,digital_competences_q3[q3_a]))
    VAR _5 = 
        CALCULATE (
            MAXX (
                ADDCOLUMNS (
                    'digital_competences_q3',
                    "score", SUMX ( FILTER ( _3, EARLIER ( digital_competences_q3[q3_a] ) = [q3_a] ), [score] )
                ),
                [score]
            )
        )
RETURN DIVIDE(_5,_4)

这篇关于DAX - PowerBi - 从数组中严格匹配子集字符串并为其赋值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！