cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PaulDBrown New Contributor
New Contributor

Re: New measure or column ?

@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

 

PaulDBrown New Contributor
New Contributor

Re: New measure or column ?

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

 

 

Mous007 Frequent Visitor
Frequent Visitor

Re: New measure or column ?

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

PaulDBrown New Contributor
New Contributor

Re: New measure or column ?

@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!

View solution in original post

Mous007 Frequent Visitor
Frequent Visitor

Re: New measure or column ?

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. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)