Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a list of items with a history and I want to get a count of specific type of item (bug) for each date and status to show a trend
This query works great to sum up all my items
Dev Complete =
IF ( Contains (LineTableVisibility,LineTableVisibility[ID], 3 ),
CALCULATE(
SUM(AllWorkItems[Story Points]),
FILTER(ALLSELECTED(AllWorkItems[Date],AllWorkItems[State] ),
AllWorkItems[Date] <= MAX(AllWorkItems[Date]) &&
AllWorkItems[State] in { "CLOSED" , "ACCEPTED", "READY FOR TEST", "IN TEST", "RESOLVED", "NEXT ER"}
)
),
Blank()
)
But What if i just wnat to get a count of how many there are for each day. I have tried this but it just gets the same total for every day
TotalBugCountByDate =
CALCULATE(
COUNT(AllWorkItems[Work Item Type]),
FILTER(ALLSELECTED(AllWorkItems[Date],AllWorkItems[State],AllWorkItems[Work Item Type] ),
AllWorkItems[Date] <= MAX(AllWorkItems[Date]) &&
AllWorkItems[State] in {"New","Resolved","Fixed","Fix_Pending","CLOSED","Open","Verification_Pending"} &&
AllWorkItems[Work Item Type] = "Bug"
)
)
Solved! Go to Solution.
HI @mwnance,
You can try to use the following measure formula if it helps:
TotalBugCountByDate =
CALCULATE (
COUNT ( AllWorkItems[Work Item Type] ),
FILTER (
ALLSELECTED ( AllWorkItems ),
AllWorkItems[Date] <= MAX ( AllWorkItems[Date] )
&& AllWorkItems[State]
IN {
"New",
"Resolved",
"Fixed",
"Fix_Pending",
"CLOSED",
"Open",
"Verification_Pending"
}
&& AllWorkItems[Work Item Type] = "Bug"
),
VALUES ( AllWorkItems[Date] ),
VALUES ( AllWorkItems[State] )
)
If the above also not hlep, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @mwnance ,
You can do something like
=
CALCULATE (
SUM ( table[value] ),
ALLEXCEPT ( table, table[column1], table[column2] )
)
Proud to be a Super User!
my table has a lot of colums and I am not looking for a sum.. I am I just want a count. of the items by a specific type
HI @mwnance,
You can try to use the following measure formula if it helps:
TotalBugCountByDate =
CALCULATE (
COUNT ( AllWorkItems[Work Item Type] ),
FILTER (
ALLSELECTED ( AllWorkItems ),
AllWorkItems[Date] <= MAX ( AllWorkItems[Date] )
&& AllWorkItems[State]
IN {
"New",
"Resolved",
"Fixed",
"Fix_Pending",
"CLOSED",
"Open",
"Verification_Pending"
}
&& AllWorkItems[Work Item Type] = "Bug"
),
VALUES ( AllWorkItems[Date] ),
VALUES ( AllWorkItems[State] )
)
If the above also not hlep, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
HI @mwnance,
Can you please share some dummy data with expected results? They should help us clarify your data structure and test to coding formula.
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Regards,
Moonlight
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |