Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newbie9
Helper I
Helper I

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

teamsportalias         name
1handballp1john
1handballp2harish
1footballp5williams
1hockeyp6vivek
1tennisp3vimal
2cricketp7ben
2ball badminton    p4thomas
2tennisp8joseph
2high jumpp3

vimal

 

 

this is table2

player       alias
johnp1
harishp2
vimalp3
thomasp4

 

calculated table 

teamsport                     alias   name           won     current count             expected count
1handballp1john121
1handballp2harish121
1footballp5williams021
1hockeyp6vivek021
1tennisp3vimal111
2cricketp7ben011
2ball badmintonp4thomas111
2tennisp8joseph011
2high jumpp3vimal111
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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
        )
    )

vyiruanmsft_0-1695263794264.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
newbie9
Helper I
Helper I

thanks @v-yiruan-msft 

 

Now the results count is coming as expected

v-yiruan-msft
Community Support
Community Support

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
        )
    )

vyiruanmsft_0-1695263794264.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors