New measure or column ?

[Mous007]

Hello everyone, 

Here is a little extract of my data:

Tests	Validity 1	Validity 2	Facility owner approval	Final validity Results
Test 1	Valid	Valid	Yes	Valid
Test 2	Non Valid	-	No	Non Valid
Test 3	-	Non Valid	Yes	Non Valid
Test 4	Valid	Not Executed
Test 5	Valid	-	No	Non Valid
Test 6	-	Valid	Yes	Non Valid
Test 7	Non valid	Non Valid	No/Yes	Non Valid
Test 8	Non valid	Non valid	No/Yes	Non Valid

Validity 1 and2 are my two conditions so far and it can be either Valid, Non Valid or Dash – (which means it was not Executed).

I don’t want to See if the tests are valid or not BUT I want to count how many tests have NOT EXECUTED validation.

 I want to count the number of times where each validity have been not executed ( - ) in one of the tests validity (1 or 2).

The result should only count the lines highlighted on the table in Bold as not executed ( - ) and should return in this case a 4 as a count.

I cannot modify the data and replace the dash’s and I have to keep it in this form.

I am not sure if I am clear enough but please feel free to post if you can think of any easy solution (for a beginner on Power Bi)

Thanks a lot

[judspud]

Hi @Mous007 

You can perform this calculation with a measure. The formula will be similar to this;

Measure = calculate(count(tests),or(valid1="-",valid2="-"))

Let me know if this helps you 🙂

Thanks,

George

[PaulDBrown]

@Mous007 

How about:

Not Executed = CALCULATE(COUNTROWS('data_table'),
FILTER('data_table',
OR(data_table[Validity 1] = "-", data_table[Validity 2] = "-")
))

[Mous007]

Thankss for ur reply George but unfortunatly the formula is returning 

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression

[Mous007]

Hi Paul, thanks for ur reply but i believe the formula is returning the wrong figures ... My data list has more then 30000 Tests and the formula is returning 22000 which im pretty certain is not the correct figure.

[judspud]

Apologies,

In that case please try the following as a column;

Column = if(or(Validity1 = "-", Validity2 = "-",1,0)

You can then sum the column in a card visual 🙂

Thanks,

George

[PaulDBrown]

@Mous007 

Are you sure?

I just set up a dummy data sample and it appears correct:

[Mous007]

Hi George, unfortunatly i still receive an error message saying: Too many arguments were passed to the OR function. The maximum argument count for the function is 2.

And Paul it is working but the total is not correct for my data (Around150000 rows) , maybe its because I have no column for rows ? Maybe i should create one first ...

[PaulDBrown]

@Mous007 

No need for a column for “rows”. I only included it to make it easier to check that the answer is correct.

How do you know it isn’t correct? 

EDIT: reading through your posts, you first mention yor dataset has over 30,000 tests, and then say the table lists more than 150,000 rows. Is each row unique? (That is, does each row reflect a unique test?)

[Mous007]

Hi there Paul, 

Sorry i forgot to mention that the tests are not unique on my data files as they are at least tested twice a year across different locations. I am of course using mapping tables for the periods and the regions and its so far working for me. 

Can you please advise me on how to proceeed with duplicate controls ?

And i know the number is smaller because according to my predecessor, the number is alwas less than 5% of tests not performed.

Thanks again for your time