Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
bob57
Helper IV
Helper IV

Seeking DAX solution

Hello,

My dataset includes this table callled Interests:

bob57_0-1641931793668.png

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

bob57_2-1641932724411.png

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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_0-1641934181642.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@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_0-1641934181642.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.