Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
mwnance
Helper I
Helper I

Count of items by date on Line Chart

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"

         )

   )

 

1 ACCEPTED 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
danextian
Super User
Super User

Hi @mwnance ,

 

You can do something like

=
CALCULATE (
    SUM ( table[value] ),
    ALLEXCEPT ( table, table[column1], table[column2] )
)









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.