Check if column contains ONLY specified value

[MasterSonic]

Hi guys,

is there a way in power query or dax to check if column (or columns if possible) contain specific value,

Eg. I want be sure that coumn "Test" contains only values "OK" for example. So i can put that info nicely on some visual.

[tamerj1]

@MasterSonic 

Please try

Check =
VAR T1 =
VALUES ( 'Table'[Column] )
VAR T2 =
FILTER ( T1, 'Table'[Column] = "Ok" )
RETURN
IF ( COUNTROWS ( T1 ) = COUNTROWS ( T2 ), "Ok", "Check your column" )

[MasterSonic]

As always your solution is superb.

Problem is,
it does not do what I want, which obviously is my fault when asking for help.

I will paste some EXCEL example. If you could have  a look please.

-Imagine that's power bi table.

- Now I sort on Test1 and looking for OK values, only where OK exists for all records in Column3.
Result I would like to get is  Value 1 as only Alpha qualifies.

- Now I sort on Test2 ,
Result is 3 as 3 distinct records from column3 have OKs in column4. 
"Beta" can not be count in as it has NO in one row.

Is this is something power bi can do?

[tamerj1]

@MasterSonic 

Column 4 can contain only "Yes" or "No", is that correct? If so please try

Count Ok =
SUMX (
VALUES ( 'Table'[Column3] ),
IF ( NOT ( "No" IN CALCULATETABLE ( VALUES ( 'Table'[Column3] ) ) ), 1 )
)

[MasterSonic]

correct

[tamerj1 · Accepted solution]

@MasterSonic 

Then you can try

Count Ok =
SUMX (
VALUES ( 'Table'[Column3] ),
IF ( NOT ( "No" IN CALCULATETABLE ( VALUES ( 'Table'[Column3] ) ) ), 1 )
)

[MasterSonic]

You are star. It works.

For future if you may, what if I would have more values -eg. Yes, No, OK, True.

Code would be very different ?

[tamerj1]

@MasterSonic 

Count Ok =
SUMX (
VALUES ( 'Table'[Column3] ),
VAR T1 =
CALCULATETABLE ( VALUES ( 'Table'[Column4] ) )
VAR T2 =
FILTER ( T1, 'Table'[Column4] = "Ok" )
RETURN
IF ( COUNTROWS ( T1 ) = COUNTROWS ( T2 ), 1 )
)

[MasterSonic]

Thanks for answer.

All rows in this particular column should have value OK.

Any other values or blank cells would rise some notification like:"please check your column"

[tamerj1]

Hi @MasterSonic 

please provide a clear example that clarifes the meaning of "ONLY"