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 

Count of BE With Epic =
COUNTROWS (
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 Without Epic] = BLANK ()
)
) + 0

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

Dear tamerj1 You are really been my inspiration and support over the past 2 weeks. Thank you.
Thank you for the DAX. 
Based on your previous DAX, this is what I used and it returns the same result. Only thing I didn't add was the +0 at the end of the DAX so that my card visual displays a 0 instead of the word blank 🙂

Count of BE Without Epic =
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
NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Without Epic] = ""
))
 
Once again that you for all your assistance. Much appreciated.
This DAX thing is truly powerful.