Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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):
But there again I may have misundestood what you are actually trying to achieve!
Proud to be a Super User!
Paul on Linkedin.
How about:
Not Executed = CALCULATE(COUNTROWS('data_table'), FILTER('data_table', OR(data_table[Validity 1] = "-", data_table[Validity 2] = "-") ))
Proud to be a Super User!
Paul on Linkedin.
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.
Are you sure?
I just set up a dummy data sample and it appears correct:
Proud to be a Super User!
Paul on Linkedin.
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 ...
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?)
Proud to be a Super User!
Paul on Linkedin.
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
Apologies since my previous example may not be what you are looking for exactly, since the measure calculates de Tests which at some stage have not been executed, but not the tests which have never been executed
If you want to count the tests which have never been tested in your sample, you need an extra measure (on top of the one I previously offered).
You can see how it works in this example:
Not Executed (at some stage) = CALCULATE(DISTINCTCOUNT(Sales[Product]); FILTER(Sales; OR(Sales[Test 1] = "-"; Sales[Test 2] = "-")))
Unique Tests Not Executed EVER = VAR calc = CALCULATETABLE(VALUES(Sales[Product]); FILTER(ALL(Sales); [Not Executed (at some stage)]=0)) VAR full = VALUES(Sales[Product]) Return COUNTROWS( EXCEPT(full; calc))
Proud to be a Super User!
Paul on Linkedin.
Hello Paul, thanks for ur support.
The first measure is working but the second one is not showing any values for me. It keep loading into the table but not showing any output and this is the first time it happens to me.
maybe its because i am using variables or an old version of powerbi
Edit
And actually the first measure is onlz returning the count of the tests i have which is 230 in total but nothing else
Many thanks again
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):
But there again I may have misundestood what you are actually trying to achieve!
Proud to be a Super User!
Paul on Linkedin.
Hi paul,
This measure is actually working now after i added a filter.
Not Executed (at some stage) = CALCULATE(DISTINCTCOUNT(Sales[Product]); FILTER(Sales; OR(Sales[Test 1] = "-"; Sales[Test 2] = "-")))
Thanks again.
Ok, I think we are getting there...
If you want to count the unique tests which have not been executed you need to use DISTINCTCOUNT.
So you measure should be along the lines of:
No executed (Unique Tests) = CALCULATE(DISTINCOUNT(data_table[Tests]); FILTER(data_table, OR(data_table [Validity 1] = "-", data_table [Validity 2] = "-")))
See example:
Proud to be a Super User!
Paul on Linkedin.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |