Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter for multiple values in one cell

Hello,   i have a data model in which the main table sometimes includes columns in which several values are included in one cell to minimize the numer of rows and columns needed.   e.g. Risks...
  • tamerj1's avatar
    tamerj1
    3 years ago

    KubenM 
    Sorry for the late response. I was trapped in a couple of meetings. I hope the following is what you're looking for.

    Count of BE Key = 
CALCULATE ( 
    COUNTROWS ( VALUES ( 'Table'[BE Key] ) ),
    FILTER ( 
        'Table',
        VAR SelectedValues = VALUES ( FilterTable[Item Value] )
        VAR String = 'Table'[Fixed Version]
        VAR Items = SUBSTITUTE ( String, " , ", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 = GENERATESERIES ( 1, Length, 1 )
        VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
        RETURN
            COUNTROWS ( INTERSECT ( T2, SelectedValues ) ) 
    )
)
KubenM's avatar
KubenM
Icon for Advocate II rankAdvocate II
3 years ago

Hello tamerj1 Even with changing the format of the column from text to whole numbers, I have no idea how to edit your original DAX to show the sum of Story Points Per each BE Size.
Please help. Thank you.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

KubenM 
Have you tried?

BE Size =
CALCULATE (
    SUM ( 'Table'[BE Key] ),
    FILTER (
        'Table',
        VAR SelectedValues =
            VALUES ( FilterTable[Item Value] )
        VAR String = 'Table'[Fixed Version]
        VAR Items =
            SUBSTITUTE ( String, ", ", "|" )
        VAR Length =
            COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 =
            GENERATESERIES ( 1, Length, 1 )
        VAR T2 =
            SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
        RETURN
            NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Size]
    )
) + 0
  • KubenM's avatar
    KubenM
    Icon for Advocate II rankAdvocate II
    3 years ago

    Hello tamerj1 I replaced SUM( ('Table'[BE Key]) ),with SUM( ('Table'[BE Size]) ), and the visual responds with exactly what I needed. Thank you soo much for assisting me in this regard. Bless You.

    Sum of BE Size =
    CALCULATE (
        SUM( ('Table'[BE Size]) ),
        FILTER (
            'Table',
            VAR SelectedValues =
                VALUES ( FilterTable[Item Value] )
            VAR String = 'Table'[Fixed Version]
            VAR Items =
                SUBSTITUTE ( String, ", ", "|" )
            VAR Length =
                COALESCE ( PATHLENGTH ( Items ), 1 )
            VAR T1 =
                GENERATESERIES ( 1, Length, 1 )
            VAR T2 =
                SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
            RETURN
                NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Size]
        )
    ) + 0
  • KubenM's avatar
    KubenM
    Icon for Advocate II rankAdvocate II
    3 years ago

    Hello tamerj1 Absolutely no reason for apologies. Through your support, I have learnt how to read some of the DAX and edit them to make them work 🙂 Once again thank you for your assistance provided. 

  • KubenM's avatar
    KubenM
    Icon for Advocate II rankAdvocate II
    3 years ago

    Hello tamerj1 I created the New Measure and dragged it onto a new page for Power BI to select a suitable visual and I get an error.  Please assist.

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    KubenM 
    Apologies. was just a typo mistake.