Forum Discussion

MCornish's avatar
MCornish
Responsive Resident
6 years ago
Solved

DAX Convert Text to Number

Hi

 

I have a table with responses to a questionaire. In the answers column there will be a mix of text and number e.g.

 

Q: Enter some text    |   This is text

Q: Enter a number    |    8

 

What I need is a measure that does an average of the question "Q: Enter a number"

 

What I have so far is:

 

VAR Numbers = SUMMARIZECOLUMNS( Responses[Reponder], Responses[Answer], FILTER( Responses, Responses[Question] = "Q: Enter a number"), "numAnsw", SUM(Responses[Answer]))

RETURN
AVERAGEX( Numbers, [numAnsw] + 0)
 
Its been through a few iterations trying to get it to work which is why its a bit weird, but in every case I end up with  "Function cannot work with values of type string"
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi MCornish 

    try a measure

    Measure = calculate(
    averagex(
        FILTER(ALL(Table2);
            NOT(ISERROR(VALUE(Table2[Answer])))
        );
    VALUE(Table2[Answer])))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi MCornish 

    try a measure

    Measure = calculate(
    averagex(
        FILTER(ALL(Table2);
            NOT(ISERROR(VALUE(Table2[Answer])))
        );
    VALUE(Table2[Answer])))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn