DAX Help - Count of Ticket IDs

[Anonymous]

Hi Community, 

I am facing an issue with a DAX command in Power BI. My current approach is to set up a new table in order to achieve my desired result. If there is any way of doing the same result by the help of a measure for example, I would be happy to implement it as well. 

My data is about certain ticket IDs depicted by a unique string. Further, I have a month, in which the ticket has been opened as well as a month, in which the ticket has been closed. 

I want to show a bar chart with all relevant months on the x-axis and the number of tickets on the y-axis:

The way how the ticket count should be calculated is the following: If a ticket is open e.g. from January 2023 to March 2023, the bar chart shows one ticket for January, February and March. Only when the ticket is closed, it should not be displayed in the following months any more (so from April 2023 to October 2023 (today) it should not display a ticket in the bar chart). 

My current table is set up with the following DAX code resulting in the following table: 

Now I would like to include the Ticket ID in a column too, so that instead of displaying the value 25 in the [Ticket Count] column, I will get the list of all 25 Ticket IDs included. That should be continued for all following months, so I can set up a relationship based on the ticket ids between this table and my main Fact_Table in order to retrieve further information. 

Does anybody have an idea how I need to adjust my DAX code for the table or alternatively set up a measure in my main fact_table to achieve the desired result? 

Appreciate your help - Thank you very much!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your datamodel.

expected result table = 
VAR _mindate =
    DATE ( YEAR ( MIN ( Data[Open_Month] ) ), 1, 1 )
VAR _maxdate =
    DATE ( YEAR ( MAX ( Data[Close_Month] ) ), 12, 31 )
VAR _t =
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "month-year", FORMAT ( [Date], "mmm-yyyy" ),
        "month-year sort", EOMONTH ( [Date], 0 )
    )
RETURN
    SUMMARIZE (
        GENERATE (
            _t,
            FILTER ( Data, Data[Open_Month] <= [Date] && Data[Close_Month] >= [Date] )
        ),
        [month-year],
        [month-year sort],
        Data[Ticket ID]
    )

[Anonymous]

Hi Jihwan_Kim, 

thats exactly what I was looking for. I implemented your approach into my Power BI file and I got the desired result. 

Thank you very much for your help!