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

Hello tamerj1  Trust that you well. Not sure if I should create a brand new entry or keep my next question in this current response flow.

I am trying to edit the original DAX you created for me to include a new column called BE Size and the visual is only returning the count of BE and not showing the sum of the Story Points per BE size. See information I used below to inform my challenge.

Sum of BE Size

Table

 

FilterTable

 

DAX

Count of BE Size =

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 Size]

))+0

 

Visual

How do I refine the DAX so that the Visual displays the Data Labels as Count of Size ie, 100 or 200 and the Total Labels to show size ie. 100 or 200, etc?

Note: The current visual isn’t reflecting the correct count.

Y23PI1 should show a stacked bar of 100 and 200 and the total label to show 300.

Y23PI2 should show a stacked bar of 200 and the total label to show 200

Y23PI3 should show a stacked bar of 200 and a total label of 200

Y23PI4 should show a stacked bar of 100 and a total label of 100

Y23PI5 should show a stacked bar of 250 and a total label of 250

 

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

    Good Morning tamerj1 It must be whole numbers. Apologies for the oversight.

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

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

     

    KubenM 

    This looks like a text data type 

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

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