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,

 

Simply change Count to Distinctcount, please.

 

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

 

Best Regards,

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

v-jiascu-msft

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