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,

AlB's avatar
AlB
Icon for Community Champion rankCommunity 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
Icon for Helper II rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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,

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

      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

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

        Hi pokdbz,

         

        Simply change Count to Distinctcount, please.

         

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

         

        Best Regards,