Forum Discussion

Helper II

7 years ago

Solved

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

Need Help

v-jiascu-msft

7 years ago

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?

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

Best Regards,

v-jiascu-msft

Microsoft Employee

7 years ago

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?

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

Best Regards,