Forum Discussion
Solved
filter context not working
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 |
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
2 Replies
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
thanks Anonymous
Now the results count is coming as expected
