Forum Discussion
Helper IVCheck if column contains ONLY specified value
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.
Then you can try
Count Ok =
SUMX (
VALUES ( 'Table'[Column3] ),
IF ( NOT ( "No" IN CALCULATETABLE ( VALUES ( 'Table'[Column3] ) ) ), 1 )
)
9 Replies
- tamerj1
Community Champion
Hi MasterSonic
please provide a clear example that clarifes the meaning of "ONLY"
MasterSonicThanks 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
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
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 )
)