Forum Discussion

bob57's avatar
bob57
Helper 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 expression that will produce the following table visualization? The table displays the cumulative score fore each interest.

Thank you,

Bob

 

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]
)

     

     

2 Replies

  • smpa01's avatar
    smpa01
    Community 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
      Helper IV
      4 years ago

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