cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Filter a column based on the value of another column

Hi All, 

 

stupid question.. but I really cannot fix it..

 

I am trying to distinct count the values in column A excluding the values in column B

 

A- ValueB-Exclude
11
2 
1 
2 
3 
3 
4 

 

The result should be 3... but if I do.. =CALCULATE(DISTINCTCOUNT(Table1[A]);ALLEXCEPT(Table1;Table1[B])) doesn't work.. how should I fix it?

 

thanks

Simone

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

i forget to add formula where 

Measure = CALCULATE(DISTINCTCOUNT(TableA[Column1]);FILTER(TableA;NOT(TableA[Column1] in VALUES(TableA[Column2]))))

View solution in original post

11 REPLIES 11
Highlighted
Super User IX
Super User IX

Re: Filter a column based on the value of another column

@simo12447 , try

calculate(distinctCOUNT(Table[A]), Table[B]<>1)

OR
calculate(distinctCOUNT(Table[A]), filter(Table,Table[B]<>1))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Filter a column based on the value of another column

thanks @amitchandak ... but I want to add to any value in column B .. not only 1 since it is an example.

Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

you can create a new column and filter the page base on true 😃  or another way around 
hope this it will help 
Test.PNG

Highlighted
Helper III
Helper III

Re: Filter a column based on the value of another column

Not really because this is just a dummy example.. but I want to exclude any value from column B that appears in column A

 

Result:=CALCULATE(COUNT(Table1[Simone]),FILTER(Table1,Table1[Simone]<>Table1[Exclude]))

 

but the result should be 3.. because I consider the 1 as a value... even if not repeated

 

exampleexample

Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

Please look atention on my solution
you saying you want to show values from column1 where is not in column 2 correct ?
then my IF create value false where the values are iqual 😃

if i filter my page where this column is all ways true you will see 1,4 and 5 excloding 2 and 3

Test.PNG

Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

the same as your exemple 😃

aa.PNG

Highlighted
Helper III
Helper III

Re: Filter a column based on the value of another column

yes, your solution works in Power BI .. and I learned a new things!! thanks for that.. but I am looking a solution for power query. Let me try to create the same and I will let you know.

Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

the same think but in mesure 😃 is this you want ?

easy.PNG

Highlighted
Helper I
Helper I

Re: Filter a column based on the value of another column

i forget to add formula where 

Measure = CALCULATE(DISTINCTCOUNT(TableA[Column1]);FILTER(TableA;NOT(TableA[Column1] in VALUES(TableA[Column2]))))

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors