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
TayVanderchys
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
Employee
Employee

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

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"?

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

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

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

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.