Forum Discussion
Helper IILastNonBlank formula help
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,
Helper IIAdding 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.
Microsoft EmployeeHi pokdbz,
Can you share your file, please? Please mask the sensitive parts first.
It's good. Please refer to the snapshot below.
Best Regards,
- v-jiascu-msft
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,
- pokdbz
- v-jiascu-msft
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,
- pokdbz
- pokdbz