Forum Discussion

bob57's avatar
bob57
Icon for Helper IV rankHelper IV
4 years ago
Solved

Seeking DAX solution

Hello, My dataset includes this table callled Interests: An interest that appears in column [Interest 1] is scored a 3, [Interest 2] a 2, and [Interest 3] a 1. Can you help by providing a DAX...
Dax Help
  • smpa01's avatar
    smpa01
    4 years ago

    bob57  firstly you need a slicer table

    slicer =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( 'Table'[Interest1] ),
            VALUES ( 'Table'[Interest2] ),
            VALUES ( 'Table'[Interest3] )
        )
    ),
    [Interest1] <> BLANK ()
)

     

    then a measure

    Measure = 
VAR _cal1 =
    CALCULATE (
        COUNT ( 'Table'[Interest1] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest1] )
    ) * 3
VAR _cal2 =
    CALCULATE (
        COUNT ( 'Table'[Interest2] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest2] )
    ) * 2
VAR _cal3 =
    CALCULATE (
        COUNT ( 'Table'[Interest3] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest3] )
    ) * 1
RETURN
    _cal1 + _cal2 + _cal3

     

    if you need subtotal

    subtotal =
SUMX (
    ADDCOLUMNS (
        slicer,
        "x",
            VAR _cal1 =
                CALCULATE (
                    COUNT ( 'Table'[Interest1] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest1] )
                ) * 3
            VAR _cal2 =
                CALCULATE (
                    COUNT ( 'Table'[Interest2] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest2] )
                ) * 2
            VAR _cal3 =
                CALCULATE (
                    COUNT ( 'Table'[Interest3] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest3] )
                ) * 1
            RETURN
                _cal1 + _cal2 + _cal3
    ),
    [x]
)

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

bob57  firstly you need a slicer table

slicer =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( 'Table'[Interest1] ),
            VALUES ( 'Table'[Interest2] ),
            VALUES ( 'Table'[Interest3] )
        )
    ),
    [Interest1] <> BLANK ()
)

 

then a measure

Measure = 
VAR _cal1 =
    CALCULATE (
        COUNT ( 'Table'[Interest1] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest1] )
    ) * 3
VAR _cal2 =
    CALCULATE (
        COUNT ( 'Table'[Interest2] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest2] )
    ) * 2
VAR _cal3 =
    CALCULATE (
        COUNT ( 'Table'[Interest3] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest3] )
    ) * 1
RETURN
    _cal1 + _cal2 + _cal3

 

if you need subtotal

subtotal =
SUMX (
    ADDCOLUMNS (
        slicer,
        "x",
            VAR _cal1 =
                CALCULATE (
                    COUNT ( 'Table'[Interest1] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest1] )
                ) * 3
            VAR _cal2 =
                CALCULATE (
                    COUNT ( 'Table'[Interest2] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest2] )
                ) * 2
            VAR _cal3 =
                CALCULATE (
                    COUNT ( 'Table'[Interest3] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest3] )
                ) * 1
            RETURN
                _cal1 + _cal2 + _cal3
    ),
    [x]
)

 

 

bob57's avatar
bob57
Icon for Helper IV rankHelper IV
4 years ago

This solves my problem. Thank you for you time and effort. Much appreciated. And a sound learning experience for me.