Forum Discussion

pokdbz's avatar
pokdbz
Icon for Helper II rankHelper 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's avatar
    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?

    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,

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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,

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft 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?

    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,

  • pokdbz's avatar
    pokdbz
    Icon for Helper II rankHelper II
    7 years ago

    v-jiascu-msft

    That is exactly what I needed!!!!!

    Thank you for all the help!