Forum Discussion

LYBridges's avatar
LYBridges
Frequent Visitor
1 year ago
Solved

DAX returning 2 values when only 1 is selected

This is basically a DAX to abbreviate words to 1 or 2 letters and then list them and eliminate trailing commas.  If only Pre-Risk is selected, it keeps returning "PR,R".  It should just return "PR". 

 

RQDel =
VAR PreRisk = IF(CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Pre-Risk"), "PR", "")
VAR Risk = IF(CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Risk"), "R", "")
VAR Quality = IF(CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Quality"), "Q", "")

RETURN
TRIM (
    IF(PreRisk <> "", PreRisk & IF(Risk <> "" || Quality <> "", ",", ""), "") &
    IF(Risk <> "", Risk & IF(Quality <> "", ",", ""), "") &
    Quality
)
  • LYBridges's avatar
    LYBridges
    1 year ago

    This is the solution that finally worked - 

     

    RQDel =
    SWITCH(
        TRUE(),
        CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "PR, R, Q",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "PR, Q",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Risk"), "PR, R",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "R, Q",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk"), "PR",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Risk"), "R",
        CONTAINSSTRING('Pipeline'[Delegated Services], "Quality"), "Q",
             "")

12 Replies

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi LYBridges please  check this

    VAR PreRisk = IF(EXACT('Pipeline'[Ops Delegated Services], "Pre-Risk"), "PR", "")
    VAR Risk = IF(CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Risk") && NOT CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Pre-Risk"), "R", "")
    VAR Quality = IF(CONTAINSSTRING('Pipeline'[Ops Delegated Services], "Quality"), "Q", "")

    RETURN
    TRIM (
        IF(PreRisk <> "", PreRisk & IF(Risk <> "" || Quality <> "", ",", ""), "") &
        IF(Risk <> "", Risk & IF(Quality <> "", ",", ""), "") &
        Quality
    )
    • LYBridges's avatar
      LYBridges
      Frequent Visitor
      1 year ago

      I spoke too soon. This now returns too few values.

      Incorrect returned value - 

      If the source coulmn has all 3, it is only returning Q.

      If the source column has Pre-Risk and Risk, it returns no abbreviation. 

       

      Correct return values 

      If the source column has Risk and Quality, it correctly returns R,Q. 

      If the source column has Pre-Risk, it correctly returns PR. 

      If the source column has Risk, it correctly returns R. 

       

      I have a multi-select/mulit-choice column that can have any combination of 18 different choices. 

      For the new column/DAX I'm trying to create, we are only interested in the choices Pre-Risk, Risk and/or Quality.

      For each row, we want to know which one or which combination of choices were selected. Due to limited space, we are trimming down to just abbreviations (PR, R, Q).

       

      So the new column will have one or any combination of PR, R, Q displayed based on the multi-select column in the source. 

      • techies's avatar
        techies
        Icon for Super User rankSuper User
        1 year ago

        Hi LYBridges please check this then

         

        VAR TextValue = 'Table'[Ops Delegated Services]
        VAR PreRiskExists = SEARCH("Pre-Risk", TextValue, 1, 0) > 0
        VAR RiskExists = SEARCH(" Risk", " " & TextValue & " ", 1, 0) > 0
        VAR QualityExists = SEARCH("Quality", TextValue, 1, 0) > 0
        VAR PreRisk = IF(PreRiskExists, "PR", "")
        VAR Risk = IF(RiskExists, "R", "")
        VAR Quality = IF(QualityExists, "Q", "")

        RETURN
        TRIM(
            CONCATENATEX(
                FILTER(
                    { PreRisk, Risk, Quality },
                    NOT(ISBLANK([Value])) && [Value] <> ""
                ),
                [Value],
                ","
            )
        )
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi LYBridges 

    Make sure to include a space before and after Risk so it becomes " Risk "

    RQDel =
    CONCATENATEX (
    FILTER (
    ADDCOLUMNS (
    { ( 1, "Pre-Risk", "PR" ), ( 2, " Risk ", "R" ), ( 3, "Quality", "Q" ) },
    "@Result", IF ( CONTAINSSTRING ( Pipeline[Ops Delegated Services], [Value2] ), [Value3] )
    ),
    NOT ISBLANK ( [@Result] )
    ),
    [@Result],
    ",",
    [Value1], ASC
    )

    • LYBridges's avatar
      LYBridges
      Frequent Visitor
      1 year ago

      Thanks for a new take on this!

       

      I tried it out and these are the results based on what is currently in the data source. 

      Correct returned values - 

      If the source column has Pre-Risk, it correctly returns PR. 

       

      Incorrect returned vaules - 

      If the source coulmn has all 3, it returns only PR,Q.

      If the source column has Pre-Risk and Risk, it returns only PR

      If the source column has Risk and Quality, it returns only Q. 

      If the source column has Risk, it returns a blank. 

       

      I have a multi-select/mulit-choice column that can have any combination of 18 different choices. 

      For the new column/DAX I'm trying to create, we are only interested in the choices Pre-Risk, Risk and/or Quality.

      For each row, we want to know which one or which combination of choices were selected. Due to limited space, we are trimming down to just abbreviations (PR, R, Q).

       

      So the new column will have one or any combination of PR, R, Q displayed based on the multi-select column in the source. 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        1 year ago

        LYBridges 

        Depending on how Risk is written in the source. It might be " Risk" or " Risk,". This is something you can find out by looking at some values of the source column. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    LYBridges Well, yeah, because "Pre-Risk" also contains the word "Risk" so that's the logical outcome of what you have coded. The real question, what do you actually expect the output to be and can you provide sample data? 

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi LYBridges , Thank you for reaching out to the Microsoft Community Forum.

    Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
    Thank you.

    • LYBridges's avatar
      LYBridges
      Frequent Visitor
      1 year ago

      This is the solution that finally worked - 

       

      RQDel =
      SWITCH(
          TRUE(),
          CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "PR, R, Q",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "PR, Q",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Risk"), "PR, R",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Risk") && CONTAINSSTRING('Pipeline'[Delegated Services], ", Quality"), "R, Q",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Pre-Risk"), "PR",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Risk"), "R",
          CONTAINSSTRING('Pipeline'[Delegated Services], "Quality"), "Q",
               "")
      • v-hashadapu's avatar
        v-hashadapu
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi LYBridges , Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please mark your insights 'Accept as solution' to help others with similar problems find it easily. 
        Thank you.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi LYBridges , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
    Thank you.