DAX Calculation for column that contain text and number

[LRCSG]

Hello,

Does any one know how to calculate a column that has text and number for data?

I can calculate the results for the Column Overall Satisfaction using this DAX formula

Overall Satisfaction Score =

DIVIDE(CALCULATE(COUNT('Table1'[Overall Satisfaction]),

FILTER('Table1','Table1'[Overall Satisfaction]>=9)),

CALCULATE(COUNT('Table1'[Overall Satisfaction]),

FILTER('Table1','Table1'[Overall Satisfaction]>=1)))

but how would I write it for the Columns that have both text and number?

Survey ID	Overall Satisfaction	Spa Satisfaction	Pool Satisfaction
1000001	10	9	10
1000002	9	8	10
1000003	10	Did not use	10
1000004	9	Did not use	9
1000005	6	8	2
1000006	8	10	7
1000007	9	8	9
1000008	8	6	9
1000009	9	Did not use	9
1000010	9	8	9
1000011	8	7	9
1000012	3	6	8
1000013	1	3	Did not use
1000014	5	9	4
1000015	8	Did not use	8

[LRCSG]

@Jihwan_Kim Wow. This is what I need and couldn't achieve. Thank you so much for your guidance and solution.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below three measures, and the attached pbix file.

I think Overall Satisfaction Score measure also has to be written in a different way, like the below.

Please check if the below results are what you expected.

Overall Satisfaction Score = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table1'[Overall Satisfaction] ),
        FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 9 )
    ),
    CALCULATE (
        COUNT ( 'Table1'[Overall Satisfaction] ),
        FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 1 )
    )
)

Spa Satisfaction Score = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table1'[Spa Satisfaction] ),
        FILTER (
            'Table1',
            'Table1'[Spa Satisfaction] * 1 >= 9
                && 'Table1'[Spa Satisfaction] <> "Did not use"
        )
    ),
    CALCULATE (
        COUNT ( 'Table1'[Spa Satisfaction] ),
        FILTER (
            'Table1',
            'Table1'[Spa Satisfaction] * 1 >= 1
                && 'Table1'[Spa Satisfaction] <> "Did not use"
        )
    )
)

Pool Satisfaction Score = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table1'[Pool Satisfaction] ),
        FILTER (
            'Table1',
            'Table1'[Pool Satisfaction] * 1 >= 9
                && 'Table1'[Pool Satisfaction] <> "Did not use"
        )
    ),
    CALCULATE (
        COUNT ( 'Table1'[Pool Satisfaction] ),
        FILTER (
            'Table1',
            'Table1'[Pool Satisfaction] * 1 >= 1
                && 'Table1'[Pool Satisfaction] <> "Did not use"
        )
    )
)