Forum Discussion

pokdbz's avatar
pokdbz
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                   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" }
        )
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,

12 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

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

     

    • pokdbz's avatar
      pokdbz
      Helper II
      7 years ago

      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.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi pokdbz,

         

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

        Last-Non-Blank-formula-help

         

        Best Regards,