Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mous007
Helper IV
Helper IV

New measure or column ?

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

1 ACCEPTED SOLUTION

@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):

 

 

Never Executed full w slicer.JPG

 

But there again I may have misundestood what you are actually trying to achieve!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

14 REPLIES 14
PaulDBrown
Community Champion
Community Champion

@Mous007 

 

How about:

 

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

@Mous007 

 

Are you sure?

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

 

Not executed.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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 ...

@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?)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

@Mous007 

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:Never Executed full.jpg

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))

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

@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):

 

 

Never Executed full w slicer.JPG

 

But there again I may have misundestood what you are actually trying to achieve!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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. 

@Mous007 

 

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:

 

 Distinct tests.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






judspud
Solution Supplier
Solution Supplier

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.