Creating a Measure: Filtering Text Column, with text from another column

[hdhillon]

This might be a simple win

Im looking to create a measure that will filter one text column called 'Answer' with another text column called 'Question'  where Question = "Recommendation / Action 1"

Then I want to combine this with the same again but where Question = "Recommendation / Action 2"

Im sure this has a simple solution 🙂

[dinesh_7780 · Accepted solution]

Hi @hdhillon ,

Refer below measures.

Get Answer for a Specific Question.

Answer for Action 1 =

CALCULATE(

    SELECTEDVALUE('Table'[Answer]),

    'Table'[Question] = "Recommendation / Action 1"

)

Answer for Action 2 =

CALCULATE(

    SELECTEDVALUE('Table'[Answer]),

    'Table'[Question] = "Recommendation / Action 2"

)

Combined Measure

Combined Actions =

VAR A1 = [Answer for Action 1]

VAR A2 = [Answer for Action 2]

RETURN

A1 & UNICHAR(10) & A2

If multiple rows exist per question → Use CONCATENATEX

Answer Action 1 =

CONCATENATEX(

    FILTER('Table', 'Table'[Question] = "Recommendation / Action 1"),

    'Table'[Answer],

    ",  "

)

Answer Action 2 =

CONCATENATEX(

    FILTER('Table', 'Table'[Question] = "Recommendation / Action 2"),

    'Table'[Answer],

    ", "

)

Combined:

Combined Actions =

[Answer Action 1] & " | " & [Answer Action 2]

If my response as resolved your issue please mark it as solution and give kudos.

[Praful_Potphode · Accepted solution]

Hi @hdhillon ,

If you are using slicer for question column then you can use SELECTEDVALUE else you can use MAX/MIN.

Combined Recommendations = 
VAR Answer1 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 1"
    )

VAR Answer2 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 2"
    )

RETURN
    -- This combines them with a separator (e.g., a comma and space)
    -- You can change " | " to UNICHAR(10) if you want a line break
    Answer1 & " | " & Answer2

If you want to use it as filter then you can try below:

Both Actions Present Flag = 
VAR Answer1 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 1"
    )

VAR Answer2 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 2"
    )

RETURN
    -- Returns 1 only if BOTH variables have a value (are not blank)
    IF( NOT ISBLANK( Answer1 ) && NOT ISBLANK( Answer2 ), 1, 0 )

you can add above measure at visual/page/report level and show where answer1 and answer 2 match.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[V-yubandi-msft]

Hi @hdhillon ,

Could you please confirm if your issue is resolved? If you need any more details or assistance, do let us know.

Thank you.

[V-yubandi-msft]

Hi @hdhillon ,
@dinesh_7780, response provides a clear and useful explanation. Please review it when you have time and let us know if you need any further details. I hope it meets your expectations.

[Olufemi7 · Accepted solution]

Hello @hdhillon, 

Here’s a simple way to achieve this using DAX.

You can use CALCULATE to filter by Question and CONCATENATEX to combine multiple answers.

Assuming your table is named Responses with columns Question and Answer:

Action 1 Answer :=
CALCULATE (
    CONCATENATEX (
        Responses,
        Responses[Answer],
        UNICHAR(10)   // line break between multiple answers
    ),
    TRIM(LOWER(Responses[Question])) = "recommendation / action 1"
)

Action 2 Answer :=
CALCULATE (
    CONCATENATEX (
        Responses,
        Responses[Answer],
        UNICHAR(10)
    ),
    TRIM(LOWER(Responses[Question])) = "recommendation / action 2"
)

Action 1 + 2 :=
VAR A1 = [Action 1 Answer]
VAR A2 = [Action 2 Answer]
VAR Part1 = IF ( NOT ISBLANK(A1), "Action 1:" & UNICHAR(10) & A1, BLANK() )
VAR Part2 =
    IF (
        NOT ISBLANK(A2),
        IF ( NOT ISBLANK(Part1), UNICHAR(10) & UNICHAR(10), BLANK() ) &
        "Action 2:" & UNICHAR(10) & A2,
        BLANK()
    )
RETURN
    CONCATENATE ( COALESCE(Part1, ""), COALESCE(Part2, "") )

Notes:

• Use Table, Matrix, or Multi-row Card visuals to see line breaks. Card visuals don’t render UNICHAR(10): use ", " instead if needed.
• If each action has only one answer, you can use SELECTEDVALUE(Responses[Answer]) instead of CONCATENATEX.
• If you want answers sorted, add a sort column (e.g., AnswerIndex or Date) and include it in CONCATENATEX.
  
  Example Output
  
  Hope this helps!
  
  
  

[Praful_Potphode · Accepted solution]

Hi @hdhillon ,

If you are using slicer for question column then you can use SELECTEDVALUE else you can use MAX/MIN.

Combined Recommendations = 
VAR Answer1 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 1"
    )

VAR Answer2 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 2"
    )

RETURN
    -- This combines them with a separator (e.g., a comma and space)
    -- You can change " | " to UNICHAR(10) if you want a line break
    Answer1 & " | " & Answer2

If you want to use it as filter then you can try below:

Both Actions Present Flag = 
VAR Answer1 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 1"
    )

VAR Answer2 = 
    CALCULATE(
        SELECTEDVALUE('Table'[Answer]), 
        'Table'[Question] = "Recommendation / Action 2"
    )

RETURN
    -- Returns 1 only if BOTH variables have a value (are not blank)
    IF( NOT ISBLANK( Answer1 ) && NOT ISBLANK( Answer2 ), 1, 0 )

you can add above measure at visual/page/report level and show where answer1 and answer 2 match.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Arul]

@hdhillon ,

Are you expecting this?

VAR Action1 =
    CALCULATE(
        SELECTEDVALUE(Table[Answer]),
        Table[Question] = "Recommendation / Action 1"
    )
VAR Action2 =
    CALCULATE(
        SELECTEDVALUE(Table[Answer]),
        Table[Question] = "Recommendation / Action 2"
    )
RETURN
Action1 & " | " & Action2

[dinesh_7780 · Accepted solution]

Hi @hdhillon ,

Refer below measures.

Get Answer for a Specific Question.

Answer for Action 1 =

CALCULATE(

    SELECTEDVALUE('Table'[Answer]),

    'Table'[Question] = "Recommendation / Action 1"

)

Answer for Action 2 =

CALCULATE(

    SELECTEDVALUE('Table'[Answer]),

    'Table'[Question] = "Recommendation / Action 2"

)

Combined Measure

Combined Actions =

VAR A1 = [Answer for Action 1]

VAR A2 = [Answer for Action 2]

RETURN

A1 & UNICHAR(10) & A2

If multiple rows exist per question → Use CONCATENATEX

Answer Action 1 =

CONCATENATEX(

    FILTER('Table', 'Table'[Question] = "Recommendation / Action 1"),

    'Table'[Answer],

    ",  "

)

Answer Action 2 =

CONCATENATEX(

    FILTER('Table', 'Table'[Question] = "Recommendation / Action 2"),

    'Table'[Answer],

    ", "

)

Combined:

Combined Actions =

[Answer Action 1] & " | " & [Answer Action 2]

If my response as resolved your issue please mark it as solution and give kudos.

[GeraldGEmerick]

@hdhillon Sorry, I'm not quite following this. Can you provide sample data for this at all? My best guess for this is something like:

My Measure =
VAR _Text1 = CALCULATE( MAX( 'Table'[Answer] ), [Question] = "Recommendation / Action 1" )
VAR _Text2 = CALCULATE( MAX( 'Table'[Answer] ), [Question] = "Recommendation / Action 2" )
VAR _Return = _Text1 & " " & _Text2
RETURN _Return

[FBergamaschi]

Hi @hdhillon 

with the info you provided I suggest, supposing the table where the Answer and Question columns are is called "Table",

CALCULATE ( 
            [Measure],
            Table[Question] = "Recommendation / Action 1" || Question = "Recommendation / Action 2",
            Table[question] = Table[Answer]

)

I am not sure I understaood what you were asking though

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI