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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors