Solved: New measure or column ? - Page 2

Mous007 · ‎10-23-2019

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

PaulDBrown · ‎10-23-2019

@Mous007

Ok, I have a feeling that the measures are correct, but I suspect you are getting the results you see because, given the 150,000 + rows of tests, and given that you carry out 230 individual tests, if you analyse the whole dataset you are finding that all tests have been carried out at some stage, and none of the tests have never "not been carried out".

150,000 rows / 230 tests = 652 rows / test (on average)!!

That is, If I am understanding what your are trying to achieve (as per your original post).

What I suggest you try is to add a slicer to your page (year for example) and play around to see if the values change.

I have tried it in my sample and the measures return what I am expecting. I have changed one of the measures to the below to allow for the slicer to work (changed the ALL in the filter to ALLSELECTED):

Unique Tests Not Executed EVER = 
VAR calc = CALCULATETABLE(VALUES(Sales[Product]); FILTER(ALLSELECTED(Sales); [Not Executed (at some stage)]=0))
VAR full = VALUES(Sales[Product])
Return
COUNTROWS(
    EXCEPT(full; calc))

Here is an example if I filter my sample data to see only rows for 2018 (which you can compare to the image I posted in the above post):