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
4 years ago

Anonymous 
The idea is applicable and can be achieved. Please provide an example of what exactly are you trying to achieve? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thx for your resposne. I'm e.g. trying to display the top5 risks. Another example would be to give a more detailed view on the risks by connecting another table where each risk is listed with more dteailed information. In general I just want to work with the individual values, use them in visuals, ect.