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". ...
  • 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",
             "")
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],
              ","
          )
      )