Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Open Tickets

Thanks to Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE fun...
Mathematical
Time Intelligence
Totals
checkner's avatar
checkner
Frequent Visitor
2 years ago

Hello, I use the following code - thank you so much for this. Now I want to put the result in a seperate table and should be like this:

DateResult of open tickets at this date
2023-01-01430
2023-01-02427


I use the following code:

 

Table = 
VAR tmpTickets = ADDCOLUMNS('Report (2)',"Effective Date",IF(ISBLANK([Geschlossen am]),TODAY(),[Geschlossen am]))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Erstellungsdatum] &&
        [Date] <= [Effective Date]
    ),
    "ID",[Stellenanforderungskennung],
    "Date",[Date]
)
RETURN tmpTable
Requisition Open = 
VAR tmpTickets = ADDCOLUMNS('Report (2)',"Effective Date",IF(ISBLANK([Geschlossen am]),TODAY(),[Geschlossen am]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Erstellungsdatum] &&
        [Date] <= [Effective Date]
    ),
    "ID",[Stellenanforderungskennung],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)