filter context not working

[newbie9]

Hi,

my problem is

I need the distinct count of sport with string consist of ball/ team but only for won = 1

the orginal column only contain team,sport,alias,name

won & current count are addcolumns

addcolumns('table',"won",if(lookupvalue('table2'[player],'table2'[alias],'table'[alias])='table'[name],1,0),

"current count", calculate(distinctcount('table'[sport]),allexcept('table','table'[team]), CONTAINSSTRING('table'[sport],"*ball*")))

I need the expected count as result instead of current count

I'm not able to use won as filter as it as in addcolumns

this is table

team	sport	alias	name
1	handball	p1	john
1	handball	p2	harish
1	football	p5	williams
1	hockey	p6	vivek
1	tennis	p3	vimal
2	cricket	p7	ben
2	ball badminton	p4	thomas
2	tennis	p8	joseph
2	high jump	p3	vimal

this is table2

player	alias
john	p1
harish	p2
vimal	p3
thomas	p4

calculated table 

team	sport	alias	name	won	current count	expected count
1	handball	p1	john	1	2	1
1	handball	p2	harish	1	2	1
1	football	p5	williams	0	2	1
1	hockey	p6	vivek	0	2	1
1	tennis	p3	vimal	1	1	1
2	cricket	p7	ben	0	1	1
2	ball badminton	p4	thomas	1	1	1
2	tennis	p8	joseph	0	1	1
2	high jump	p3	vimal	1	1	1

[newbie9]

thanks @Anonymous 

Now the results count is coming as expected

[Anonymous · Accepted solution]

Hi @newbie9 ,

You can create two measures as below to get it, please find the details in the attachment.

Won = 
VAR _alias =
    SELECTEDVALUE ( 'table'[alias] )
VAR _player =
    CALCULATE (
        MAX ( 'table2'[player] ),
        FILTER ( 'table2', 'table2'[alias] = _alias )
    )
RETURN
    IF ( ISBLANK ( _player ), 0, 1 )

Expected count =
VAR _team =
    SELECTEDVALUE ( 'table'[team] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'table'[sport] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            'table'[team] = _team
                && CONTAINSSTRING ( 'table'[sport], "*ball*" )
                && [Won] = 1
        )
    )

Best Regards