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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
newbie9
Helper I
Helper I

filter context is not working in add columns

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
3 REPLIES 3
newbie9
Helper I
Helper I

my expected output is "EXPECTED COUNT" column

 

my base model has 2 tables - TABLE & TABLE2 

using those 2 table I have created calculated column WON & CURRENT COUNT

 

my WON column is right but my CURRENT COUNT is not

my CURRENT COUNT column should only apply if WON = 1 but since WON is used as a addcolumn I'm not able to use it as a filter 

 

 

this is my dax

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

 

parry2k
Super User
Super User

@newbie9 could you explain the logic of what you are trying to achieve and what is the expected output?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

my expected output is "EXPECTED COUNT" column

 

my base model has 2 tables - TABLE & TABLE2 

using those 2 table I have created calculated column WON & CURRENT COUNT

 

my WON column is right but my CURRENT COUNT is not

my CURRENT COUNT column should only apply if WON = 1 but since WON is used as a addcolumn I'm not able to use it as a filter 

 

 

this is my dax

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.