Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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,
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" )
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.
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
Hi @pokdbz,
Simply change Count to Distinctcount, please.
CountCompleted = CALCULATE ( DISTINCTCOUNT ( Table1[Role] ), Table1[Status] = "Completed" )
Best Regards,
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.
Hi @pokdbz,
Can you share your file, please? Please mask the sensitive parts first.
It's good. Please refer to the snapshot below.
Best Regards,
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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |