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 ) ) 
    )
)
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 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.