Filter a column based on the value of another column

[simo12447]

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- Value	B-Exclude
1	1
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

[amitchandak]

@simo12447 , try

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

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

[simo12447]

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

[Bogas]

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

[simo12447]

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

example

[Bogas]

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

[Bogas]

the same as your exemple 😃

[simo12447]

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.

[Bogas]

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

[Bogas · Accepted solution]

i forget to add formula where 

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