Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Id like to create a measure or calculated column that calculates the total occuerrence of early for each item. For example, in the table below "Item A" would have a value of 2 because "early" appears two times with "Item A". "Item B" would have a value of 0 because early does not apply. "Item C" will have 3 because early appears three times
SalesOrder # | Salesorderline# | Create Date | Invoice Date | Item | Segment | Site | Status |
100 | 1 | 1/1/2024 | 1/6/2024 | A | front | Bank | Early |
100 | 2 | 2/1/2024 | 2/6/2024 | A | front | School | Early |
101 | 1 | 1/2/2024 | 1/7/2024 | A | back | Kitchen | Late |
101 | 2 | 2/2/2024 | 2/7/2024 | B | side | Park | Late |
102 | 1 | 3/1/2024 | 3/6/2024 | C | back | House | Early |
102 | 2 | 4/1/2024 | 4/6/2024 | C | front | Store | Early |
102 | 3 | 5/1/2024 | 5/6/2024 | C | min | Mall | Early |
103 | 1 | 2/1/2024 | 2/6/2024 | D | max | Store | Early |
Desired output
SalesOrder # | Salesorderline# | Create Date | Invoice Date | Item | Segment | Site | Status | count of early |
100 | 1 | 1/1/2024 | 1/6/2024 | A | front | Bank | Early | 2 |
100 | 2 | 2/1/2024 | 2/6/2024 | A | front | School | Early | 2 |
101 | 1 | 1/2/2024 | 1/7/2024 | A | back | Kitchen | Late | 2 |
101 | 2 | 2/2/2024 | 2/7/2024 | B | side | Park | Late | |
102 | 1 | 3/1/2024 | 3/6/2024 | C | back | House | Early | 3 |
102 | 2 | 4/1/2024 | 4/6/2024 | C | front | Store | Early | 3 |
102 | 3 | 5/1/2024 | 5/6/2024 | C | min | Mall | Early | 3 |
103 | 1 | 2/1/2024 | 2/6/2024 | D | max | Store | Early | 1 |
Solved! Go to Solution.
@dwhittaker1
Add an additional filter to your calculated column as follows, I assume you need the year from the Creation Date. you can change it to a different column as well.
Early Count =
CALCULATE(
COUNTROWS(Table01),
ALLEXCEPT( Table01 , Table01[Item ]),
Table01[Status ] = "Early",
YEAR( Table01[Create Date ]) = 2024
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
This calculated column formula works
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item ]=EARLIER(Data[Item ])&&Data[Status ]="Early"))
Hope this helps.
@dwhittaker1
Add this calculated column:
Early Count =
CALCULATE(
COUNTROWS(Table01),
ALLEXCEPT( Table01 , Table01[Item]),
Table01[Status] = "Early"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@dwhittaker1
Add an additional filter to your calculated column as follows, I assume you need the year from the Creation Date. you can change it to a different column as well.
Early Count =
CALCULATE(
COUNTROWS(Table01),
ALLEXCEPT( Table01 , Table01[Item ]),
Table01[Status ] = "Early",
YEAR( Table01[Create Date ]) = 2024
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |