cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TayVanderchys Frequent Visitor
Frequent Visitor

Distinctly counting where not blank - Help needed

Hi,

 

So basically when a person visits a store they mark down whether a PRODUCT is "Ranged on entry", "ranged on exit", "out of stock on exit" or "out of stock on entry"..

 

Each row is one PRODUCT at a particular STORE.

 

Products and Stores have unique identifiers.

 

If they don't fill these out correctly and they leave them BLANK.. this is what I consider a "Missing status":

 

Missing status =
COUNTROWS (
    FILTER (
        'Dist',
        ISBLANK ( 'Dist'[IsRangedOnEntryAll] ) && ISBLANK ( 'Dist'[IsRangedOnExitAll] )
            && ISBLANK ( 'Dist'[IsOosOnExit] )
            && ISBLANK ( 'Dist'[IsOosOnEntry] )
    )
)

111.JPG

 

 

When I know there is a missing status, I'll have someone go into store and remedy this.

 

The issue is where i've highlighted red..

As you can see store number 2 with product 465 was visited a second time to remedy this blank status.

But when it comes time for the calculation which is going through the row context it will still give store 2/product 465 a missing status. This isn't what I want..

 

What I want to know is, if this product at this store is eventually completed at a LATER VISIT.. I don't want the historic version (Where they are blank), to cause the missing status to be 1.. I want this to be DISCLUDED in the calculation..

 

I was thinking this could be done with some sort of IF statement? maybe combining the store number and product key and doing a distinctcount? 

 

I want to be able to say "Okay, let me check product 465 at store 2" the results will tell me the missing status is null.. 

 

This should also work when aggregating the results.. I should be able to see "Total missing status = x" without totalling the products that have been visited at a later visit.

 

Any help is appreciated.

 

Thank you!!

5 REPLIES 5
v-ljerr-msft Super Contributor
Super Contributor

Re: Distinctly counting where not blank - Help needed

Hi @TayVanderchys,

 

According to your description above, the formula below should work in your scenario.Smiley Happy

Missing status =
DISTINCTCOUNT ( Dist[Product] )
    - COUNTROWS (
        FILTER (
            'Dist',
            (
                ISBLANK ( 'Dist'[IsRangedOnEntryAll] ) && ISBLANK ( 'Dist'[IsRangedOnExitAll] )
                    && ISBLANK ( 'Dist'[IsOosOnExit] )
                    && ISBLANK ( 'Dist'[IsOosOnEntry] )
            )
                = FALSE ()
        )
    )

r2.PNG

 

Regards

TayVanderchys Frequent Visitor
Frequent Visitor

Re: Distinctly counting where not blank - Help needed

Thank you for your response, I seem to be coming up with a negative number.. (An extremely large one, too)...

 

Unless I should be creating a calculated column of "Missing Status"?

v-ljerr-msft Super Contributor
Super Contributor

Re: Distinctly counting where not blank - Help needed

Hi @TayVanderchys,

 

Based on my test, it should work as a measure against the sample data you posted above.

 

To further investigate on the issue, could you post some sample data which can reproduce the issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

TayVanderchys Frequent Visitor
Frequent Visitor

Re: Distinctly counting where not blank - Help needed

Hi,

 

Thanks for the responses, but still haven't got a solution yet.

 

I've uploaded an sample file to:

 

https://www.dropbox.com/s/hxkrrjxwwbr0si2/MissingStatusProblem.pbix?dl=0

 

 

The real answer should be 0 missing status..

 

As basically that means for each of the employee keys, we have got NONBLANKS for at least 1 of the rows.. which means the CURRENT missing status should be 0..

 

But instead it accumulates the calculation

v-ljerr-msft Super Contributor
Super Contributor

Re: Distinctly counting where not blank - Help needed

Hi @TayVanderchys,

 

Based on my test, the formula below should work in your scenario.Smiley Happy

Missing status =
DISTINCTCOUNT ( Dist[EmployeeKey] )
    - CALCULATE (
        DISTINCTCOUNT ( Dist[EmployeeKey] ),
        FILTER (
            'Dist',
            (
                ISBLANK ( 'Dist'[IsRangedOnEntryAll] ) && ISBLANK ( 'Dist'[IsRangedOnExitAll] )
                    && ISBLANK ( 'Dist'[IsOosOnExit] )
                    && ISBLANK ( 'Dist'[IsOOSOnEntry] )
            )
                = FALSE ()
        )
    )

r3.PNG

 

Regards