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
pokdbz
Helper II
Helper II

LastNonBlank formula help

I need to get the total for the Count of Last Status = "Completed" per store

 

Result

Store 2 = 3

Store 3 = 1

 

 

Here is how the data is setup

Store                Role                   Status

2                       Bakery               In Progress

2                       Bakery               Completed

2                       Seafood            Completed

2                       Deli                   Completed

2                       Meat                 In Progress

3                       Bakery               Completed

3                       Deli                    In Progress

 

This gets me the last status.  But I'm not sure how to get it to count only the Completed.

 

xmeasure =
        LASTNONBLANK
        (
            'StoreTasks'[Status],
            'StoreTasks'[Status] IN { "Completed" }
        )
1 ACCEPTED SOLUTION

Hi @pokdbz,

 

I'm afraid you can't find the last status with that formula. Please refer to lastnonblank-function-dax.

LastStatusText =
LASTNONBLANK ( 'StoreTasks'[Status], 
'StoreTasks'[Status] IN { "Completed" }  // it returns true or false, which never be a blank.
)

Let's take the Store 0002 and the role Bakery as an example, the last status always "Completed". What should it be?

Last-Non-Blank-formula-help3

 

If you are sure you did it in the right way, please try this formula to get the result.

Measure =
SUMX (
    SUMMARIZE (
        StoreTasks,
        StoreTasks[Role],
        [StoreNumber],
        "Status", [LastStatusText]
    ),
    IF ( [Status] = "Completed", 1, 0 )
)

Last-Non-Blank-formula-help4

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
AlB
Super User
Super User

Hi @pokdbz

Try this:

1. Set Table1[Store] in the rows of a matrix visual

2. Set this measure in values:

 

 

CountCompleted =
CALCULATE ( COUNT ( Table1[Role] ), Table1[Status] = "Completed" )

 

@AlB

 

Not quite what I was looking for.  There can only be one "Completed" per Role

 

So Store 2

                        Deli                 Bakery           Seafood        Meat                    Total

2                      Completed     Completed    Completed    In Progress          3

 

So when it is aggregated to Store it would look like this and produce a Total of 3.

Hi @pokdbz,

 

Please refer to the snapshot below. @AlB's solution works. So what's the issue?

Last-Non-Blank-formula-help

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft@AlB

 

When I run AIB's measure it produces this output

 

Store                  Bakery              Deli           Meat            Seafood            Total

2                         14                    2                4                  4                        22

 

Since I only need to know if there was a completed the result(not the count of every complete) it should only have a 1 or a 0 to make the output look like this

Store                  Bakery              Deli           Meat            Seafood            Total

2                         1                      1                0                  1                   3

Hi @pokdbz,

 

Simply change Count to Distinctcount, please.

 

CountCompleted =
CALCULATE ( DISTINCTCOUNT ( Table1[Role] ), Table1[Status] = "Completed" )

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

Adding distinct just does the count on the whole dataset.  There needs to be some incorporation of the LastNonBlank "Status"

 

Adding distinct produces this

Store                  Bakery              Deli           Meat            Seafood            Total

2                         1                      1                1                  1                   4

 

It should look like this, because Meat did not have a status of Completed

Store                  Bakery              Deli           Meat            Seafood            Total

2                         1                      1                0                  1                   3

 

 

Store                Role                   Status

2                       Meat                 In Progress

2                       Meat                 Completed

2                       Meat                 Completed

 

Since for Meat the last status is "In Progress" this should not be counted.

 

Hi @pokdbz,

 

Can you share your file, please? Please mask the sensitive parts first.

It's good. Please refer to the snapshot below.

Last-Non-Blank-formula-help2

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

 How do you upload a pbix file?

Hi @pokdbz,

 

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts first.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @pokdbz,

 

I'm afraid you can't find the last status with that formula. Please refer to lastnonblank-function-dax.

LastStatusText =
LASTNONBLANK ( 'StoreTasks'[Status], 
'StoreTasks'[Status] IN { "Completed" }  // it returns true or false, which never be a blank.
)

Let's take the Store 0002 and the role Bakery as an example, the last status always "Completed". What should it be?

Last-Non-Blank-formula-help3

 

If you are sure you did it in the right way, please try this formula to get the result.

Measure =
SUMX (
    SUMMARIZE (
        StoreTasks,
        StoreTasks[Role],
        [StoreNumber],
        "Status", [LastStatusText]
    ),
    IF ( [Status] = "Completed", 1, 0 )
)

Last-Non-Blank-formula-help4

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

That is exactly what I needed!!!!!

Thank you for all the help!

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.