Open Tickets/Issues Report per Day

[Simeon]

Hi Friends,

i found some similar Threads to my problem but not solved in PowerBI as i need to do.
I have Ticket Data with all Information to track the progress.
I want to create a line graph to show how many tickts are open per Day.
For this i need in Dax an calculation of the cumulative frequency but i'm new to PowerBI and Dax is totaly a new are for me.

Can somebody help me with this?
My Data set is as following. Every Day the Data is Updated (Report Date in F).
E.g. 2021-05-30 are 3 Open Tickets and one Day later two Open Tickets left.

Open Tickets will have in any case the state "Open".





Thanks in advance for any help, very appreciated.

Simeon

 

[Chihiro]

I'd recommend uploading sample workbook with desired result. DAX is very contextual in terms of its evaluation.

Alternately, you can do this without creating any DAX measure. By simply structuring your Pivot Table (ex: Report Date in Row field. State as slicer, and Count of Ticketnumber as value field, filter on Open status).

P.S. is your data correct? You have ticket with close date, but state is still open...

 

[Simeon]

Hi @Chihiro thanks for your reply.
Youre absolutly right, this was mistake because i had to anonimze the Data for the Screesnhot.
I uploaded now a sample Dataset.

Unfortunately, i have to do it with DAX because i'm building a PowerBI solution.
With other Tools i might have some ideas how to solve this but with DAX and PowerBI not..

So maybe somebody can help me based on my sample Dataset. This would be gorgeous.
This is my Code so far wondering how i can Filter on tickets with state Open and if i'm on the right way?

Cumulative Open Tickets =
CALCULATE (
    COUNT ('Ticketdata[Ticketnumber] ),
    FILTER (
        ALL ('Ticketdata[Date]')
        'Ticketdata[Date] <= MAX ('Ticketdata[Date])
    )
)

Simeon

 

[Chihiro]

Something like below?

Cumulative Open Tickets =
CALCULATE (
    COUNT ('Ticketdata[Ticketnumber] ),
    FILTER (
        ALL ('Ticketdata[Date]')
        'Ticketdata[Date] <= MAX ('Ticketdata[Date])&& 'Ticketdata[State]="Open"
    )
)

 

[Simeon]

Hi @Chihiro i tested your solution and i was sure it will work but it runs on an error:

A single value for column 'State' in table 'Ticketdata' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I assume that this means that PowerBI cannot work with the different stages right?
My workaround would be to create a extra column and put a 1 for every row which has the state "Open" or is there a better way to solve this issue?

Thanks for your help, very appreciated.

Simeon

EDIT: My idea with 1 and 0 in extra row don't work. Same error message.

 

[Chihiro]

Hmm? That error means one of your calculation is returning multiple values, where single value is expected.

Try something like below then.
If you want to present it in Matrix Visual. Showing "Open" ticket by owner.

=
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[State] = "Open"
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

If you want all open ticket to be returned regardless of context. Something like...

=
VAR maxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[State] = "Open" && 'Table'[Date] <= maxDate )
    )

Though... if I understand your requirement, "'Table'[Date] <= xxx" is redundant. As you are looking to return all open tickets.

 

[Simeon]

Hi @Chihiro you are absolutly right.
It's working now.
Thanks for your help .


Simeon