Forum Discussion

hdhillon's avatar
hdhillon
Advocate III
8 months ago
Solved

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

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"...
DAX
  • Anonymous's avatar
    Anonymous
    8 months ago

    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's avatar
    Praful_Potphode
    8 months ago

    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

  • Olufemi7's avatar
    Olufemi7
    8 months ago

    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!


GeraldGEmerick's avatar
GeraldGEmerick
Super User
8 months ago

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